Results 1 to 2 of 2
  1. #1
    NewGuy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    5

    Finding records without matches in two fields on another table, that meet a criteria

    Ok, so here's the situation:
    I have a table (ProgramReg) listing of 2000+ people who have enrolled in a program. This program has two categories, one of which requires a current first aid certification. Table ProgramReg contains the following fields: "First_Name", "Last_Business_Name", and "ProgID" (which identifies which category they are enrolled in: either 1101 or 1102).

    I have another table (CurrentCert) of clients with current first aid certifications. The fields in that table are: "FirstName", "LastName" and the location at which they certified. The certification is offered once a year, and is valid for 4 years so the CurrentCert table changes once yearly.



    What I want to do is run a query that will show me all people enrolled in the 1101 category (that's the one that requires certification) who do not appear in the CurrentCert table.

    I have already created composite keys on each table using first and last names (the only thing the two tables have in common).

    How do I create a query that lists only the records from ProgramReg where field ProgID = 1101, but which do NOT have a record that matches (both their first and last names) on the CurrentCert table?

    Any help is greatly appreciated...I'm going cross-eyed from thinking about this!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Create a query that links the two tables through the first and last name.

    In the ProgID field put the criteria 1101

    If you are diligent about updating your currentcert table (removing people who have expired or, better yet have a date of expiration) you can do a couple of things

    If the CurrentCert table only contains people with current certifications use the criteria:

    IS NOT NULL in the FIRST or LAST name of your CURRENTCERT table

    If you currentcert table has a date of expiration you can use a criteria on the certificate expiration date of:

    >= [Enter the Starting Date]

    This would show you only people who have a certification after a certain starting date (say the beginning of your term)

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

Similar Threads

  1. Query for records that do not meet criteria
    By survivo01 in forum Queries
    Replies: 3
    Last Post: 12-16-2012, 05:45 PM
  2. Replies: 2
    Last Post: 11-28-2012, 10:47 AM
  3. Summing when no records meet criteria
    By clew3 in forum Queries
    Replies: 6
    Last Post: 11-05-2012, 11:37 AM
  4. Replies: 3
    Last Post: 08-22-2012, 03:51 PM
  5. Replies: 2
    Last Post: 03-31-2009, 11:15 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