I inherited an Access 2003 database with this setup:

TableOne
TableOneId pocOne pocTwo
1 2 3
2 2 4
3 1 2

TableTwo
TableTwoId Name
1 Jones
2 Smith
3 Edwards
4 Camden

I currently have this query where I need to find all the records that
match the criteria (Smith) and was wondering how I can eliminate the
Union All and put it in one SQL:

select * from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId = TableOne.pocOne
where Name = 'Smith'
UNION ALL
select * from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId = TableOne.pocTwo
where Name = 'Smith'

On Mon, 25 Feb 2008 16:50:01 -0800 (PST), "teser3 (AT) hotmail (DOT) com"
<teser3 (AT) hotmail (DOT) com> wrote:

The reason these queries are giving you grief is that the database is
not normalized.
If people can select pocs, there should be three tables:
tblPocs
pocID PK
pocName UniqueIdx

tblPeople (like your Table2)

and a junction table to express the Many-to-many relation between
them:
tblPocsForPeople
pocID PK
PersonID PK

Once you have this in place the query becomes trivial.

-Tom.

teser3 (AT) hotmail (DOT) com wrote:
Tom Stiphout's answer is the correct one, but if you have to keep your
structure, one option is to create a query that UNIONs the data in TableOne:

SELECT PocOne as Poc
FROM TableOne
UNION
SELECT PocTwo as Poc
FROM TableOne;

Save this as Q_Pocs, or whatever name, and use it in place of tables One and
Two in your queries. Note that doing this eliminates the distinction
between PocOne and PocTwo - whatever they are. You didn't say you needed
them.

teser3 (AT) hotmail (DOT) com wrote:

One thing you could do is create a new query (Query/New/Design/Close).
Add Table1 and then add Table2 2 times. Create link lines between Pod1
to Table2 and a link line between Pod2 and Table2_1. Now dbl-click on
the link lines and change it to (All recs in Table1 and matching records
in Table2). Do the same for Table2_1.

Now drag your table1 fields down and the name fields from Table2 and
Table2_1. Put "Smith" in the first criteria row for Table1 and "Smith"
in the second criteria row for Table2_1. This creates an Or statement
if you look at the SQL (View/SQL)

I suppose you could create a calculated column if you want
ResultName : IIF(Table2!Name = "Smith",Table2!Name,Table2_1!Name)
You could even tell which field it is.
PodField : IIF(Table2!Name = "Smith","Pod1","Pod2")

Decadence
http://www.youtube.com/watch?v=GukLy-jOfNo