Query to find who has not taken a course 2

[jlancton]

Hello,

I have a database that tracks courses taken by employees. I have tblEmployees, tblClasses, and tblClassesTaken holding who took what.

I now need a report that lists who has NOT taken a given course. I already have a qry/report to show who has taken a particular course, but I can't figure out how to list who hasn't. Any help will be greatly appreciated.

Thanks!

-Jeff

 

[AlexCuse]

Without seeing anything about your data or knowing how they link, the best anyone can give is a shot in the dark. This may work (I'm not sure if I got the parentheses on the joins right, I know Access likes to do some weird things with those):

select tblClasses.Class
, tblEmployees.Employee
from
(tblClasses inner join tblClassesTaken
on tblClasses.ClassID = tblClassesTaken.ClassID)
right join
tblEmployees
on tblClassesTaken.EmpID = tblEmployees.EmpID
where tblClassesTaken.EmpID is null
and tblClasses.Class = [A Given Class]

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jlancton]

Sorry for leaving out info...

tblEmployees
empID
lastname
firstname
...

tblClasses
classID
className
ClassDate
...

tblClassesTaken
classesTakenID
empID
classID

tblClasses and tblEmployees have one-to-many relationships with tblClassesTaken.

I tried the code, and got a Join Not Supported error.

Using Access 2002

 

[dhookom]

Please share your SQL that raised the error. If you tried Alex's SQL as written, it would certainly error.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AlexCuse]

Since I can't really test this without any data, why not just try something that I'm pretty sure will work

select a.Class
, tblEmployees.Employee
from
(
select tblClasses.Class,
tblClassesTaken.empID
from
tblClasses inner join tblClassesTaken
on tblClasses.ClassID = tblClassesTaken.ClassID
where tblClasses.Class = [A Given Class]
) a
right join
tblEmployees
on a.EmpID = tblEmployees.EmpID
where a.EmpID is null

See if this gets what you are after. If it doesn't I will set up tables and play around with it a bit.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jlancton]

Interesting... It takes the query, allows it to save, and will even run, though it doesn't generate the correct results. Then when I go back to design view, it's changed the ('s to [', and then won't re-save because it says incorrect bracketing.

I feel we're getting closer...

 

[PHV]

An old fashioned way:
SELECT *
FROM tblEmployees
WHERE empID NOT IN (SELECT empID FROM tblClassesTaken WHERE classID = [A Given Class])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886