Search for records with no matching foreign key

[Little Penny]

I have two tables that are linked by primary and foreign keys which
are tblClubs (Primary) and tblmembers (Foreign) and a One to Many
relationship. I also have a form with the master table being tblClubs
and a sub form for the tblmembers. How can I create a query that only
show the records of the tblClubs that don't have a matching foreign
key in the tblmembers.

Is this possible



Thanks



Little Penny

 

[Jerry Whittle]

Sure. Access comes with an Unmatched Query wizard. Where to find the wizard
depends on the version of Access.

 

[Little Penny]

I'm using Access 2003

 

[Little Penny]

I got it

Thanks


strSQL = "SELECT tblClubs.* FROM tblClubs " & _
"LEFT JOIN tblMembers ON " & _
"tblClubs.ClubID = tblMembers.MembersID " & _
"WHERE tblMembers.MembersID Is Null" & ";"

 

[KARL DEWEY]

Start by creating a query in design view and putting both tables in the space
above the grid.
Click on the primary key field of tblClubs and drag to the foreign key field
of tblmembers. Double click the connecting line and select all records from
tblClubs and only those that match from tblmembers.
Click on the asterisk in tblClubs and drag to field row of the grid. Click
on the foreign key field og tblmembers and drag to a diffferent block of the
field row of the grid.
In the criteria row under the tblmembers foreign key field enter --
Is Null

Run query.