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