find records in one table not in another table

  • Thread starter Thread starter Sophie
  • Start date Start date
S

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.
 
Create a new query, and select the Find Unmatched Query Wizard. It'll walk
you through the process.
 
Douglas said:
Create a new query, and select the Find Unmatched Query Wizard. It'll walk
you through the process.
Click to expand...
yeah, unless your find unmatched query wizard never displays field names
like mine (agghhhh!)...
 
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.
Click to expand...

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]
 
Back
Top