Combine Query 1

[oaklanders]

In my Access 2003 I have this setup:

TableOne
pocOne    pocTwo
2         3
2         4
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'

 

[fneily]

Sure, if you're willing to create a correct Access table. Your TableOne is not normalized. It should look something like: TableOneID TableTwoId POC
example records:
Tab1 2 PocOne
Tab2 2 PocTwo
Tab3 2 PocOne
Tab4 3 PocOne
etc.

Why? From the first normal form, your table has duplicate column names: POC POC (drop the "one" and "two"). What happens in the future when there's a POCThree? Then the table structure changes. What if noone is in a PocOne or PocTwo? Then you have empty fields in a column. Both cause, in theory, variable length records. Not allowed.

If you correct the table as suggested, then you can obviously see the query is simplified. As you can see from the example records, Smith would then pick up three records from TableOne quite easy.

 

[oaklanders]

Thanks, this database is already up and running and I cant change it or touch it unfortunately. I assume I will have to keep the query I have?

 

[PHV]

What about this ?
select * from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId = TableOne.pocOne
or TableTwo.TableTwoId = TableOne.pocTwo
where Name = 'Smith'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Another way to try:
select * from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId In (TableOne.pocOne,TableOne.pocTwo)
where Name = 'Smith'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[oaklanders]

Thanks