Need query to find employees who have not had required training


G

Guest

I am creating a database to track employee training. I have created 3 tables:
my first table is for general employee information, my second is for employee
training history which is linked to the first table by the employee #. This
table list all of the classes that the employee has taken to include course
name, course#, class #, date completed and date re-current training is due.
My third table is linked to the second table by the course name and contains
all of the courses available for the employee to take. Some of these classes
are mandatory classes and I have used a yes no field to identify them.
I need to build a query to identify all of the employees who have not had
any or all of the mandatory classes. This is starting to get the best of me.
Is there a way to do this or am I beating my head against the wall?

Your help with this would be greatly appreciated
insp36rls
 
Ad

Advertisements

J

Jeff Boyce

You can build a query that lists all course that are mandatory, right?

You can build a query of all courses taken an employee, right?

Create an unmatched query (use the query wizard to help) ... what courses
are in the first query that are not in the second?

If you need to do this for ALL employees at once, first create a list of all
required courses for all employees -- use a cartesian product query (tables
are NOT joined) between mandatory courses and employees.

Then run your unmatched query from mandatory courses against this cartesian
product query.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
G

Guest

Jeff: Thanks for the suggestion, I will try it out and let you know how it
goes.
Again thanks, I really appreciate the help.
insp36rls
 
Ad

Advertisements

G

Guest

Jeff: The first unmatched query works great. It identifies the course that
has not been accomplished by a single employee. Now the second query on the
other hand does not give me the same result. could you please break this down
into steps. I think that I might not be building the ALL employees at once,
first create a list of all
required courses for all employees --list properly and do I need to specify
criteria in the unmatched query I created for a single employee and is this
used to check for items not listed in my all employee list. I have tried
several combinations with no help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top