returning a certain qualification

[Martin Hopkins]

Hello,

I am running a report that lets me know if certain people do not have a
certain qualification.

All works well if the employee has no qualifications and the return is true.

However if an employee has many qualifications including the qualification
in the search or not, I get them returned in the report output.

The query works from a field value from a report and searches records for
name, dept, <>qualification.

Becuase employee B holds qualification required plus others, he still
appears in thecreport, because, i think, he stills holds other quailications
<>qualification search.

How can I say, return any employee who does not hold the required
qualification and ignore ny others held.

thanks in advance for any help.

Martin Hopkins

 

[Douglas J. Steele]

Create a subquery that returns the id of all of the users who do have the
qualification. Use that with NOT IN to return those who don't have the
qualification.

SELECT Employee.ID, Employee.Name
FROM Employee
WHERE Employee.ID NOT IN
(SELECT EmployeeID
FROM EmployeeQualification
WHERE Where QualificationID = 123)

(or whatever your tables and fields are)

 

[David F Cox]

Do you have a text field something like:- "B Sc, M.Sc, 50 yds Crawl" ?

or individual records:

B Sc
M.Sc
50 yds Crawl

?

 

[Martin Hopkins]

Doug,

Thanks, worked a treat.

Martin

Create a subquery that returns the id of all of the users who do have the
qualification. Use that with NOT IN to return those who don't have the
qualification.

SELECT Employee.ID, Employee.Name
FROM Employee
WHERE Employee.ID NOT IN
(SELECT EmployeeID
FROM EmployeeQualification
WHERE Where QualificationID = 123)

(or whatever your tables and fields are)