Combine queries that is now using Union All

[teser3@hotmail.com]

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.TableT woId = TableOne.pocOne
where Name = 'Smith'
UNION ALL
select * from TableTwo
Inner Join TableOne
on TableTwo.TableT woId = TableOne.pocTwo
where Name = 'Smith'

[Tom van Stiphout]

Re: Combine queries that is now using Union All

On Mon, 25 Feb 2008 16:50:01 -0800 (PST), "teser3@hotmail .com"
<teser3@hotmail .comwrote:

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:
tblPocsForPeopl e
pocID PK
PersonID PK

Once you have this in place the query becomes trivial.

-Tom.

>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.TableT woId = TableOne.pocOne
>where Name = 'Smith'
>UNION ALL
>select * from TableTwo
>Inner Join TableOne
>on TableTwo.TableT woId = TableOne.pocTwo
>where Name = 'Smith'

[DFS]

Re: Combine queries that is now using Union All

teser3@hotmail. com wrote:

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.TableT woId = TableOne.pocOne
where Name = 'Smith'
UNION ALL
select * from TableTwo
Inner Join TableOne
on TableTwo.TableT woId = TableOne.pocTwo
where Name = 'Smith'

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.