Querying data where no composite key exists?

[Jillops]

My first post so please go easy...any help gratefully received.

I have an Employee Training database, and need to run a query to show
all employees who haven't done a particular course.

I have the following tables:

EMPLOYEE
Employee ID Primary KEY
Name
Job Title

BOOKING
Employee ID Composite KEY
Course ID Composite KEY
Date Booked
Date Expires

COURSE
Course ID Primary Key
Name
Details

Linked as follows:

EMPLOYEE --- 1 to Many --- BOOKING --- Many to 1 --- COURSE

A simple access form of Employee with the sub form Booking, allows
users to select an employee on the main form, and on the booking sub
form (datasheet) select the courses an individual has completed. Every
employee needs to have completed a particular course, (Course ID number
4) but this form/sub form will not have an entry on the sub form for
course ID 4, unless it has been completed at least once by the employee
(ISO standards).

How can I write a query to display all employees who have not completed
Course ID 4 when there is no entry on the database linking an Employee
ID to Course ID 4 in the first place?

Many thanks,

Chris.

 

[Jeff Boyce]

Chris

Your "Bookings" table has those folks who HAVE completed Course#4.

Your "Employee" table has all folks.

Consider using the query wizard to help you build an "unmatched" query,
finding all folks in "Employee" who are not in a query of "Bookings" that
show Employees w/ Course#4 (you'll need to build this query first).

Regards

Jeff Boyce
<Access MVP>

 

[Jillops]

Excellent, thanks Jeff, that worked a treat!

Cheers,

Chris.