Results 1 to 3 of 3
  1. #1
    Stan Denman is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    31

    Query to find no foreign key

    I have three table to house information about clients and their cases. My client table, tblClaim and tblClaimDetails. So I would have in tblClaim a autonumber ClaimID field with a foreign field ClientID from tablClients. And in tlbClaimMilestones I have autonumber ClaimMilestone with foreign field ClaimID. I want to identify clients where we have failed to complete data in tlbClaim and tblClaimMilestones. So I want all records in tlbClient where the field ClientID does not exist as a foreign field in tlbClaim, and secondly I want all clients where yes we have a record in tlbClaim (field ClientID exists as a foreign field in tblClaim) but a record does not exist in tblClaimMilestones (i.e., a ClaimID value does not exist as a foreign field in tblClaimMilestone). Switching up the join properties does not seem to work.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    3,473
    You switch the join to an outer join (arrow pointing from Client table to Claim table) and add the foreign key ClientID from Claims and put Is Null in the criteria row. Look at the unmatched query entry in the ribbon's query wizard.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Stan Denman is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    31
    Thanks for much Vlad. That worked. I appreciate it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query with Foreign Keys
    By BawdyB in forum Queries
    Replies: 5
    Last Post: 05-15-2013, 06:47 PM
  2. Append Query with a foreign key
    By dougperk in forum Queries
    Replies: 1
    Last Post: 06-15-2012, 05:30 PM
  3. Replies: 10
    Last Post: 05-08-2012, 09:17 AM
  4. advantage of foreign key in query
    By learning_graccess in forum Queries
    Replies: 1
    Last Post: 04-20-2012, 03:00 AM
  5. Replies: 2
    Last Post: 04-19-2012, 11:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums