Training DB Help

[Richard]

I have a db that tracks employee training. I have 3 table. An Emplyee table
with employee info, a Class table that lists all the classes including a
manadatory flag (some classes are mandatory, some are not). The third table
records tha classes that the employees have taken.

What I am being asked to do now, is create a report that lists the employees
who have not taken the mandatory classes.

I can query the training table to see who has taken mndatory and
non-mandatory classes. But, this next query is supposed to look up all
emplyees in the company to see if they have taken the mandatory training.

Your help is greatly appreciated. Thanks
r

 

[OfficeDev18 via AccessMonster.com]

Richard,

I understand that you understand queries, but need some methodology. I will
respond in kind.

I would break this down into two tasks. First, identify those employees who
took SOME BUT NOT ALL of the mandatory classes. Put the employee numbers (or
names), along with the MISSED classes, in a temporary table. Then identify
those employees who took NONE of the mandatory classes, and append that data
(again, employee numbers (or names), along with the MISSED classes) to the
table. Design the report with the table as your report's Record Source.

Hope this sketch helps,

Sam

 

[John Vinson]

I have a db that tracks employee training. I have 3 table. An Emplyee table
with employee info, a Class table that lists all the classes including a
manadatory flag (some classes are mandatory, some are not). The third table
records tha classes that the employees have taken.

What I am being asked to do now, is create a report that lists the employees
who have not taken the mandatory classes.

I can query the training table to see who has taken mndatory and
non-mandatory classes. But, this next query is supposed to look up all
emplyees in the company to see if they have taken the mandatory training.

Your help is greatly appreciated. Thanks
r

A NOT EXISTS query may be the best bet here:

SELECT Employee.*
FROM Employee
WHERE NOT EXISTS
(SELECT EmployeeID FROM Enrollment
WHERE Enrollment.ClassID IN
(SELECT ClassID FROM Classes WHERE Mandatory = True))

using your own table and fieldnames of course.

John W. Vinson[MVP]

 

[Richard]

Thank you John. I am making progress. I took your SQL and tweaked it to match
table names. Let's say I wanted to add theClassNames to identfy which Classes
were missing.

 

[Richard]

John,
After getting the query to work. I went and added a record for someone who
attended the mandatory training. I was expecting to see one less record in
the query. I received no records.

 

[John Vinson]

Thank you John. I am making progress. I took your SQL and tweaked it to match
table names. Let's say I wanted to add theClassNames to identfy which Classes
were missing.

Hrm. That's tougher! Please post your current SQL.

John W. Vinson[MVP]

 

[Richard]

SELECT tblEmployees.*
FROM tblEmployees
WHERE Not Exists (SELECT EmployeeID FROM tblTraining
WHERE tblTraining.ClassID IN
(SELECT ClassID FROM tblClasses WHERE Mandatory = True));