Prev: Query to Find Records with only 1 transaction
Next: Type mismatch in JOIN expression. (Error 3615) Join data stored a
From: thrusty on 9 Nov 2009 13:28
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!
From: John Spencer on 9 Nov 2009 14:00

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

thrusty wrote:
> 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!
From: Marshall Barton on 9 Nov 2009 14:03
thrusty wrote:

>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")

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Query to Find Records with only 1 transaction
Next: Type mismatch in JOIN expression. (Error 3615) Join data stored a