Query to find ee's with training not completed

[shaunburke]

I am working with a training database. I am a fresh newbie to access (more of an excel user).

I need to find employee's who have never completed a certain training (DPW Medication Training), which is mandatory.

I have the following tables:
Employee data TBL - Contains active ee's names
CompletedTrainings - Contains all trainings completed by individual
Events - name of all the trainings, including mandatory trainings

I have the following queries:
DPW Med Training - Lists all who have completed the training
DPW-Med-Last - Lists when each employee last had the training
Med_01 - Lists those who have had the training, but need it renewed

Any thoughts?

 

[ajetrumpet]

I need to find employee's who have never completed a certain training (DPW Medication Training), which is mandatory.

I have the following tables:
Employee data TBL - Contains active ee's names
CompletedTrainings - Contains all trainings completed by individual
Events - name of all the trainings, including mandatory trainings


Any thoughts?

Here's one thought:

Give the exact layout of all your tables, and what the relationships are between them, and someone will be able to give you a very good answer to your problem.

 

[shaunburke]

CompletedTrainings:
Employee ID
Training Title
Date Began
Trainer 1
Hours
Date Ended
Internal
External
Annual
Mandatory
EOC
DDAPL
Licensing

Employment Data TBL:
loc
Active
Employee ID
Gender
Race
Education
Status
Position
DOH
dept
begindate
enddate
termdate

 

[shaunburke]

Events:
Training ID
Training Title
Mandatory
Annual
Licensing

Employee data TBL and Completed Trainings r/s's:
Employee ID

Completed Trainings and Events r/s's:
Mandatory
Annual
Licensing

 

[ajetrumpet]

Employee data TBL and Completed Trainings r/s's:
Employee ID

I assume this is a one-to-many? I hope so!

If it is, that's all you need to get what you want. Use the query grid to JOIN the two tables with an INNER JOIN. Select the fields you want from both the tables, and the program will join them for you.

I need to find employee's who have never completed a certain training (DPW Medication Training), which is mandatory.

So, you will probably want a NOT IN clause to find all the employees that do not have a record in the "Completed Trainings" table with the DPW Medication Training name in the "Training Title" field. So, after you use the grid, the SQL will probably look something like this:

SELECT [Employment Data].[Employee ID]

FROM [Employment Data] INNER JOIN [Completed Training] ON
   [Completed Training].[Employee ID] = [Employment Data].[Employee ID]

WHERE [Completed Training].[Training Title] NOT IN 

(SELECT [Completed Training].[Training Title]
   FROM [Completed Training]
      WHERE [Training Title] = "DPW Medication Training");

This is just a representation of what the SQL will look like when you use the grid. The criteria section is probably closer to being correct.

One other thing too:

Completed Trainings and Events r/s's:
Mandatory
Annual
Licensing

Why are there 3 joining fields here? You only need one. I would drop the 3 (they are probably intermediates, aren't they?), and just do one, a one-to-many on the "Training Title" field. The Events table probably has unique records in it, so a join on one field should be fine.

 

[shaunburke]

I don't think it's pulling correctly. Here's the SQL:

SELECT [Employment data TBL].[Employee Id], [Employment data TBL].Active, [Employment data TBL].loc
FROM [Employment data TBL] INNER JOIN CompletedTrainings ON [Employment data TBL].[Employee Id] = CompletedTrainings.[Employee Id]
WHERE ((([Employment data TBL].loc)<>"CALC") AND (("DPW Medication") Not In (SELECT [CompletedTrainings].[Training Title] FROM [CompletedTrainings] WHERE [Training Title] = "DPW Medication Training")));

 

[ajetrumpet]

I don't think it's pulling correctly. Here's the SQL:

SELECT [Employment data TBL].[Employee Id], [Employment data TBL].Active, [Employment data TBL].loc
FROM [Employment data TBL] INNER JOIN CompletedTrainings ON [Employment data TBL].[Employee Id] = CompletedTrainings.[Employee Id]
WHERE ((([Employment data TBL].loc)<>"CALC") AND (("DPW Medication") Not In (SELECT [CompletedTrainings].[Training Title] FROM [CompletedTrainings] WHERE [Training Title] = "DPW Medication Training")));

You need to reference the field at the highlighted mark, not the actual value you are evaluating, just like it is written in the previous SQL statement I posted.

Replace (("DPW Medication") with (([CompletedTrainings].[Training Title])