By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,576 Members | 1,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,576 IT Pros & Developers. It's quick & easy.

Combine queries that is now using Union All

P: n/a
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'
Feb 26 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Mon, 25 Feb 2008 16:50:01 -0800 (PST), "te****@hotmail.com"
<te****@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:
tblPocsForPeople
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.TableTwoId = TableOne.pocOne
where Name = 'Smith'
UNION ALL
select * from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId = TableOne.pocTwo
where Name = 'Smith'
Feb 26 '08 #2

P: n/a
DFS
te****@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.TableTwoId = TableOne.pocOne
where Name = 'Smith'
UNION ALL
select * from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId = 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.
Feb 26 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.