Seeing what does not match in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
Click to expand...
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!
 
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
Click to expand...

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?
 
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
 
Thanks once again - it only takes one little thing to really mix things up in
a query, lol. That worked perfectly =)
 
Back
Top