Qry - Exceptions between two tables?

[Guest]

I have two client tables in Access, both tables contain client records but
one table contains more clients records than the other. What I'm trying to
do is compare data from tables and qry/report/make table (whatever will work
at this point!) to show clients that are missing or are in one table an not
the other. I'm guessing there is an easy way to do this but I haven't had
any luck today. All advice is appreciated!

 

[Guest]

You will probably have some luck with the IN() clause, for example:

SELECT FirstName, LastName FROM tblContacts1 WHERE NOT IN(SELECT FirstName,
LastName FROM tblContacts2)

This will give you all the contacts in tblContacts1 that are not in
tblContacts2 (based on first and last names).

You can add more fields and change the table names etc. This should give you
a start...

Steve

 

[Guest]

Thanks SteveM. I gave your code a try and I keep getting a syntax error
(missing operator) in query expression 'NOT IN(SELECT SSN FROM CMHCtbl)'

I'm new to SQL so I'm sure I'm missing something.

 

[Rick A.B.]

I have two client tables in Access, both tables contain client records but
one table contains more clients records than the other. What I'm trying to
do is compare data from tables and qry/report/make table (whatever will work
at this point!) to show clients that are missing or are in one table an not
the other. I'm guessing there is an easy way to do this but I haven't had
any luck today. All advice is appreciated!

Use an unmatched query. The query wizzard with create one for you.