Quey to return records in table A not in table B

[RLA]

Hi --
I am trying to write a query that returns records in table A that are not in table B. It seems like you should be able to do it in a union query but I haven't figured out how. Please help! Thanks.

 

[smedvid]

SELECT Table1.Field1, Table2.Field1
FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
WHERE (((Table2.Field1) Is Null));

Index related fields also...

htwh... Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[gol4]

use not in

select * from tableA where indexd filed not in ( select Indexed field from tableb)

or us a left join and look for nulls

select tbl1.fld1, tbl2.fld1
from tbl1 left join tbl2 ON tbl1.fld1 = tbl2.fld1
where (((tbl2.fld1) is null));

 

[WildHare]

You might also check my FAQ about joins in the tables/relationships forum to help to understand these things. Hopefully you'll find it useful.

FAQ700-1681

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm