Search for records with no matching foreign key

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

Sure. Access comes with an Unmatched Query wizard. Where to find the wizard
depends on the version of Access.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Little Penny" wrote:

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
.

I'm using Access 2003



On Sat, 13 Feb 2010 17:53:01 -0800, Jerry Whittle
wrote:

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

I got it

Thanks


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








On Sat, 13 Feb 2010 22:57:49 -0500, Little Penny
wrote:

I'm using Access 2003



On Sat, 13 Feb 2010 17:53:01 -0800, Jerry Whittle
wrote:

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

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.

--
Build a little, test a little.


"Little Penny" wrote:

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
.