Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    159

    Unanswered: query to find not in list help needed

    I have a task to find people who have not competed training.
    I have a list of all people by ssn (qry1)and I have a list of all people who have completed training (qry2) What is the best way to get a list of peope who have NOT completed training? It would be in effect qry1 - qry2
    Would this be an outer join? How do I set that up?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yup, an outer join it is...

    SELECT qry1.ssn, qry2.training_complete
    FROM qry1 LEFT JOIN qry2 ON qry1.ssn = qry2.ssn
    WHERE qry2.ssn IS NULL


    *Side Note: Many people frown on using ssn as a primary key do to privacy concerns as well as flexibility. In general, having a primary key representing "meaningful data" could put you in an uncomfortable position. For instance, what happens if an employee gets married and changes their name, but you want to preserve the name they had on all activity prior to getting married. How can you add them again if you're referencing ssn instead of an arbitrary key?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •