Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Talking Unanswered: Find a record that doesn't exist

    I have a training database with multiple courses. Certain staff do course1 and thereafter course2. Course1 is only done once and course2 is a refresher done annually. I want the query to find staff that have done course2 but not course1.

    Thanks.
    Thomas J Marshall

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Without the details of your db design its difficult to provide any pointers. Perhaps you could give an idea of what your design looks like.

    There are various possible solutions have a look at using left joins (ie include all records fromt he table to the left of the join, and all records from the table (where a match is found in the right table) THis can also be done by creating a left join in the syery wizard. Place you tables, define links by selecting a column in the parent table and draggin accrsooss to the column in the child tables approapriate column.

  3. #3
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Talking

    Quote Originally Posted by healdem
    Without the details of your db design its difficult to provide any pointers. Perhaps you could give an idea of what your design looks like.

    There are various possible solutions have a look at using left joins (ie include all records fromt he table to the left of the join, and all records from the table (where a match is found in the right table) THis can also be done by creating a left join in the syery wizard. Place you tables, define links by selecting a column in the parent table and draggin accrsooss to the column in the child tables approapriate column.
    My 2 main tables are tb_staff (staff_id) to tb_courses_done (courses_done_id, staff_id).
    Thomas J Marshall

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Subquery option:

    SELECT staff_id
    FROM tb_staff t1 INNER JOIN tb_courses_done t2 on t1.staff_id = t2.staff_id
    WHERE t2.courses_done_id = 2 AND t1.staff_id NOT IN (SELECT staff_id FROM tb_courses_done WHERE courses_done_id = 1)

    Domain function option:

    SELECT staff_id
    FROM tb_staff t1 INNER JOIN tb_courses_done t2 on t1.staff_id = t2.staff_id
    WHERE t2.courses_done_id = 2 AND DCount("t1.staff_id", "tb_courses_done", "courses_done_id = 1") = 0


    Your backend structure is a little strange. I would recommend creating a table for courses and using the course_id in your courses_done table.
    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
  •