If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Search for records with no matching foreign key
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 . |
#3
|
|||
|
|||
Search for records with no matching foreign key
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. |
#4
|
|||
|
|||
Search for records with no matching foreign key
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. |
#5
|
|||
|
|||
Search for records with no matching foreign key
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 . |
Thread Tools | |
Display Modes | |
|
|