Query to Find Records from Table A that are not in Table B - HELP!!!

[Vem786]

Hi:
I have 2 tables Table A & Table B. Table A has Field "Name_TableA" & Table B has Field "Name_TableB". There are certain common records between BOTH the tables. But my query has to FIND the Records that EXIST in Table A but NOT IN Table B. So I use this query --

Select [Table A].[Name_TableA] FROM [Table A] WHERE NOT EXISTS (select NULL FROM [Table B] WHERE [Table A].[Name_TableA] = [Table B].[Name_tableB]);

But I get to see records that EXISt in Table B as well.

Any Ideas on this Query???


Thanks!!!

 

[CosmoKramer]

Check out Access help for the Find Unmatched Query Wizard......

Hoc nomen meum verum non est.

 

[TheRiver]

I think you would want something like this:
SELECT [TableA].*
FROM [Name_TableA] LEFT JOIN [TableB] ON [TableA].[Name_TableA] = [TableA].[Name_TableB]
WHERE ([TableB].[Name_TableB] Is Null);

Try it and see how you get on.

 

[TheRiver]

Sorry I think it shoudl be this ignore previous message.


SELECT [TableA].*
FROM [TableA] LEFT JOIN [TableB] ON [TableA].[Name_TableA] = [TableB].[Name_TableB]
WHERE ([TableB].[Name_TableB] Is Null);

 

[JonFer]

Vem786 - your Not Exists clause should start with "Select * from ...", not "Select Null"

You can also use TheRiver's SQL which is what the Unmatched Records Wizard would generate.

 

[WildHare]

Vem - you might also find helpful this FAQ here, my attempt to explain joins in all their marvelous glory...

faq700-1681


HTH

Jim

Don't be sexist - Broads hate that.
Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

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