Inverted Query

[thrusty]

Can't seem to figure this one out...has to be simple though.

I have a database of employee's training records. I need to find
those employees who have not had the course "BPS".

Right now I have a complex query: Table1 has the employee's personal
info and Table 2 has all courses taken...They are left joined by ID
numbers.

Here's what I have:
SELECT *
FROM EmployeeData LEFT JOIN EmployeeTraining ON EmployeeData.ID =
EmployeeTraining.EmployeeID
WHERE ((((([EmployeeTraining].[CourseName])="BPS")) Is Null));

Of course this yields me people who have no courses entered at all...
Any suggestions?
Thanks!

 

[John Spencer]

SELECT E.*
FROM EmployeeData as E LEFT JOIN
(SELECT EmployeeID
FROM EmployeeTraining
WHERE CourseName ="BPS") as T_BPS
ON E.ID = T_BPS.EmployeeID
WHERE T_BPS.EmployeeID is Null

You can also use a subquery in the where clause

SELECT E.*
FROM EmployeeData as E
WHERE E.ID NOT IN
(SELECT EmployeeID
FROM EmployeeTraining
WHERE CourseName ="BPS")

OR
SELECT E.*
FROM EmployeeData As E
WHERE NOT EXISTS
(SELECT *
FROM EmployeeTraining
WHERE CourseName ="BPS"
AND EmployeeTraining.EmployeeID = E.ID)

Or you can do this is two queries
(query one) get all employees from Employee training who have taken the
training save the query

Now use that query and the EmployeeData table in an unmatched query to return
employees that need the training.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

Can't seem to figure this one out...has to be simple though.

I have a database of employee's training records. I need to find
those employees who have not had the course "BPS".

Right now I have a complex query: Table1 has the employee's personal
info and Table 2 has all courses taken...They are left joined by ID
numbers.

Here's what I have:
SELECT *
FROM EmployeeData LEFT JOIN EmployeeTraining ON EmployeeData.ID =
EmployeeTraining.EmployeeID
WHERE ((((([EmployeeTraining].[CourseName])="BPS")) Is Null));

Try using something more like:

SELECT EmployeeData.*
FROM EmployeeData
WHERE EmployeeData.ID Not In
(SELECT EmployeeTraining.EmployeeID
FROM EmployeeTraining
WHERE EmployeeTraining.CourseName="BPS")