An Opposite Report

[Ant]

Hello, and thanks in advance for your help.

I have three tables in my database as follows:

1. Employee - self explanatory
2. Qualfications - a list of licences, certificates, endorsements
3. Employee/Qualification Join - to link the various types of work
qualifications each employee has.

From this I have created two reports that show:

1. For each employee, the qualifications they hold and the expiry dates of
those qualifications
2. For each qualifictation, which employees have them.

MY PROBLEM IS....

I was just asked to create a report that lists the employees that don't have
a particular qualification. For example, print the list of employees that
don't have a first aid certificate.

I have tried various queries, but have had no luck. Any suggestions.

Regards
Ant

 

[Allen Browne]

Time to delve into subqueries.

See the "Identifying what is NOT there" section of this article:
http://allenbrowne.com/subquery-01.html#NotThere

 

[KARL DEWEY]

Try this --
SELECT Employee
FROM (tblEmployee LEFT JOIN [Employee/Qualification] ON tblEmployee.EmpID =
[Employee/Qualification].EmpID) LEFT JOIN tblQualfications ON
[Employee/Qualification].QualID = tblQualfications.QualID
WHERE tblQualfications.Qualfication = "First Aid" AND
tblQualfications.QualID Is Null
ORDER BY Employee;