find records in one table not in another table

[Sophie]

hi to all

I'm sure this is easy, but as a new user I could use some help.
tblAllMembers lists records for all present and past members of a sports
club. tblCurrentMembers has the same fields but lists only currently
registered members. Both tables have primary key memID.

I need a query that lists all records in tblAllMembers that are NOT in
tblCurrentMembers. Any help is appreciated.

 

[Douglas J. Steele]

Create a new query, and select the Find Unmatched Query Wizard. It'll walk
you through the process.

 

[Sharon V]

Create a new query, and select the Find Unmatched Query Wizard. It'll walk
you through the process.

yeah, unless your find unmatched query wizard never displays field names
like mine (agghhhh!)...

 

[John W. Vinson]

hi to all

I'm sure this is easy, but as a new user I could use some help.
tblAllMembers lists records for all present and past members of a sports
club. tblCurrentMembers has the same fields but lists only currently
registered members. Both tables have primary key memID.

I need a query that lists all records in tblAllMembers that are NOT in
tblCurrentMembers. Any help is appreciated.

If the wizard is being uncooperative, here's how you can "roll your own".
Create a Query adding tblAllMembers and tblCurrentMembers to the query grid.
If Access doesn't do so for you automatically, join the two tables by MemID.

Select the Join line in the design window by right clicking it, and select
Properties. Choose option 2 (or maybe 3) - "Show all records in tblAllMembers
and matching records in tblCurrentMembers".

Select whatever fields you want to see from tblAllMembers, and ONLY the MemID
from tblCurrentMembers. Put a criterion on it of

IS NULL

to explicitly select only those records which do *not* have a match.

John W. Vinson [MVP]