Searching 2 tables with one query and getting distinct output

[trend]

Hello, I am using vb and an access db.

I have 4 tables in my access db file.

I need to have a query that can search through 2 tables at the same time, and output the results in a manor that I will not get duplicate answers (because the 2 tables can contain the same data sometimes).

I have seen code like this:

SELECT *
FROM myTable1
WHERE (myTable1.ID = 5)
UNION ALL
SELECT *
FROM myTable2
WHERE (myTable2.ID = 5)
UNION ALL
SELECT *
FROM myTable3
WHERE (myTable4.ID = 5);

but if the tables looked like this:

mytable2 -- (ID=5 results-) green,orange, red,blue
mytable1 --(ID=5 results-) blue,purple,cyan,yellow

I would get this output:
green,orange,red,blue,blue,purple,cyan,yellow.

So 2 instances of blue.. I only want one result of blue.

Could I use distinct command some how?


thanks for any help!!
Lee

 

[PlausiblyDamp]

Try using just union ratherthan union all

 

[trend]

Great!

Simple and quick.

thanks! Lee