Help with Query Design (1 Viewer)

[BJS]

I have 2 tables "tblEmployee" and tblEmployeeCourses; they are joined by EmpId.

tblEmployeeCourses, simply shows "course code", "course name", "institution", and "date taken" fields.

From this table, I can determine which employees have taken which courses.
But how do I determine which employees have not taken a particular course (prompt for course code).

**Note: There is no table that contains a list of courses, since there will be over 2000. The courses are simply entered for an employee as they are taken.

Thanks for the help.
BJS

 

[cheuschober]

Okay you need to be more clear as to the relevant fields in each table but I am going to make the assumption that your tblEmployeeCourses table is keyed with the EmpID and course code, institution, and date taken fields.

You want what is called an 'unmatched' query.

First Select the fields you want returned

SELECT tblEmployee.EmpID, tblEmployee.EmpName... etc...

Then you create an outer join to your course table to take all Employees and only those records from the Course table that have matching records.

FROM tblEmployee LEFT JOIN tblEmployeeCourses ON tblEmployee.EmpID = tblEmployeeCourses.EmpID

Lastly you add your criterion. In this case you select the course code your're querying against and then you tell the system to only accept records for Employees who do not have a record for said course code.

WHERE tblEmployeeCourses.CourseCode = [code] AND tbl.EomployeeCourses.EmpID = Null

There. Hope that solves your issue.
~Chad