Seeing what does not match in a query

[Guest]

I've been building a training database for where I work. The database has to
contain information about classes that employees are required to take,
classes that have been taken, etc.

Table: Employee
------------------
Emp ID
Emp F. Name
Emp L. Name

Table: Class
------------------
Class ID
Class Name

Table: Job-Specific Training Due
---------------------------------
Class ID
Emp ID

Table: Class Attendance Roster
---------------------------------
Class ID
Emp ID


My problem comes from trying to track which employees have not met their job
specific training. I have been able to track which employees have met their
training, but when I try to build the query for just the opposite, I get no
results even though I've put test data in with some employees that have not
attended a class they were required to be at.

I've tried:

WHERE [Class Attendance Roster].[Class ID] Not in [Job-Specific Training
Due].[Class ID] AND [Class Attendance Roster].[Emp ID] Not In [Job-Specific
Training Due].[Emp ID]

Any help would br greatly appreciated!

 

[Jeff L]

You need to set up some outer joins. You probably have inner joins set
up in your query design. Your SQL would look like this:

Select Employee.*

From Employee a Inner Join [Job-Specific Training Due] b

ON a.[Emp ID] = b.[Emp ID]
Left Outer Join [Class Attendance Roster] c
ON b.[Class ID] = c.[Class ID]
Where c.ClassId IS Null

This query should give you the names of all employees that have not met
their job requirements.

Hope that helps!

 

[Guest]

Hey Jeff,

That's definiately in the right direction - just having problems inputting
last join.

Left Outer Join [Class Attendance Roster] c
ON b.[Class ID] = c.[Class ID]
Where c.ClassId IS Null

I keep getting a syntax error(missing operator), referencing back to this
portion of the code.

I made sure that each data type matched, and that the code was referencing
the proper columns so it's not that =S Any suggestions?

 

[Jeff L]

Here's how I did it:

SELECT a.Fname, a.Lname
FROM (Employee AS a LEFT JOIN TrainingDue AS b ON a.EmpID = b.EmpID)
LEFT JOIN AttendanceRoster AS c ON (b.EmpID = c.EmpID) AND (b.ClassID =
c.ClassID)
WHERE (((c.ClassID) Is Null));

Just sub your tables and fields where necessary

 

[Guest]

Thanks once again - it only takes one little thing to really mix things up in
a query, lol. That worked perfectly =)