Find out who HASN'T had a particular Training Course

  • Thread starter Thread starter Craash!
  • Start date Start date
C

Craash!

Access 2003 using 2000 File format

Relating to my issue, I have three tables.

Table 1
name=tbl_employees
Key=SS Number
Contents Personal Data
Total records ~490

Table 2
name=tbl_course_list
Key=course_name
Contents Details about each Training course (number, notes,
description)
Total records ~200

Table 3
name=tbl_training_actions
Key=Auto Generated
Contents, each record contains course name (limited to records from
table 2), course date, course instructor, and the Social Security
Number of the Pupil (limited to records from table 1). So, a class of
30 people attending "Fall Protection Training" will generate 30
records.
Total records ~thousands

What I want to be able to do is supply a query with a "course_name"
criteria (that's the EASY part) and be rewarded with the people who
HAVEN'T sat for the class.

Any help is appreciated.
 
Create a query for Table3 filtered on the desired course. Return the
CourseName and SSN fields. Use this query in the query below:

SELECT Table1.SSN From Table1 Left Join FirstQuery On Table1.SSN =
FirstQuery.SSN WHERE FirstQuery.CourseName Is Null;

This is known as an "unmatched query".
 
Perhaps

SELECT * FROM tbl_employees RIGHT OUTER JOIN tbl_training_actions ON
tbl_employees.[SS number] = tbl_training_actions.[SS Number] WHERE
tbl_training_actions.Course_Name = Criteria AND
IsNull(tbl_training_actions.[SS Number])

This assumes that Course_Name is a real name, not a meaningless ID, in
which case you would need to add an inner join based on that id before
using the real name from tbl_course_list.
Pavel
 
Back
Top