Exclusion: How to show when staff have not completed a training fo

[neetkleat]

I am trying to set up a staff training database that will help figure out
what trainings they still need to take for their positions.

Here are the mock tables and fields I have set up.

Table 1:
Position Name
Required Training 1
Required Training 2
Required Training 3
Required Training 4

Table 2:
Staff Name
Staff Position Name
Training Taken 1
Training Taken 2
Training Taken 3
Training Taken 4

Next, I want to set up a query that will show what Trainings they have not
taken for Their Position.

Basically when for when Staff Position Name = Position Name, show if
Training Taken 1 is not equal to Required Training 1, etc.

How do I do that? I know it's an exclusion query, but I can't figure out how
to do it. I'm missing something.

 

[Allen Browne]

Wherever you see repeating fields "Training 1", "Training 2", etc), it
always means you need a related table. On position name needs many training
units, so you need a related table to hold the training units for the
position.

It's actually considerably more complex. You need at least these 5 tables:

1. A table of position names, with PositionID as primary key

2. A table of training units, with UnitID as primary key

3. A table of the training units required for each position, with fields:
- PositionID what position
- UnitID what unit is required for that position
So if the position "bank teller" requires 3 units, it will have 3 records in
this table.

4. A table of staff, with fields:
- StaffID primary key
- PositionID position this staff member currently holds (assuming that
you have no part timers with multiple positions, and you don't need to keep
track of what positions people held in the past.)

5. A table of the training staff have actually done, with fields:
- StaffID who completed this training
- UnitID what training they completed
- CompletionDate Date/Time when they completed it.

You can now create a query combining tables 4 and 3 to show the units each
staff member should have done. Save. Then use the Unmatched query wizard to
find records in that query that are not in #5.

(It can actually get more complex if refresher units are required after a
certain time, or if you need to track training in groups or the completion
of tasks or attendance within training, or ...)

 

[neetkleat]

Not quite working. What relationships would have to exist? (Sorry, I am VERY
new at this)

 

[Allen Browne]

I indicated what the primary key fields were. The matching name in the other
table is the foreign key.

So, the one-to-many relationships would be like this:
tblPosition.PositionID => tblStaffPosition.PositionID
tblUnit.UnitID => tblPositionUnit.UnitID
tblStaff.StaffID => tblStaffUnit.UnitID
tblUnit.UnitID => tblStaffUnit.UnitID

 

[Allen Browne]

If you are completely new to this kind of relationship, this article might
help:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

 

[vanderghast]

If your tables are like:

requirements ' your table1
Position Training ' fields
Securest Bio101
Securest FirstAid101
Securest English
Securest Spanish
Fireman ... ' data sample

where there is NO dup on the couples, and, you got it, one record per
different (position, training) couple.


and

skills ' your table2
Employee Training 'fields
Mary English
Mary FirstAid101
John English
John Spanish
.... ' data sample

again, without repetition of each couple of data, and one record per
(employee, training skill owned by the employee).

Then, the following query should work:


--------------------
SELECT r.position, s.employee
FROM requirements AS r INNER JOIN skills AS s
ON r.training = s.training
GROUP BY r.position, s.employee
HAVING COUNT(*) = (SELECT COUNT(*)
FROM requirements AS x
WHERE x.position = r.position)
----------------------


giving, one record at a time, which position can be filled by which
employee. Add an ORDER BY clause to collect the records near each other, by
employee, or position, as required.

How it works? If a given position requires 6 skills, and a given employee
have only 5 of them, the INNER JOIN will acts like an intersection (set
theory) and then the count, now 5, won't match the initial count of 6, and
the given employee won't be kept for that given position. The inner join
will acts like an intersection *only if* the records are not duplicated, in
the original tables. So, it may be much safer, by design, to add that
constraint on the tables.

Note that the table design:

Employee, skill1, skill2, skill3, ....


WON'T work as nicely, not at all. In fact, most of the job is in getting the
RIGHT table design!



Vanderghast, Access MVP

 

[vanderghast]

I just thought it could be easier to replace the sub-query with an outer
join:

--------------------
SELECT r.position, s.employee
FROM requirements AS r LEFT JOIN skills AS s
ON r.training = s.training
GROUP BY r.position, s.employee
HAVING COUNT(*) = COUNT(s.training)
----------------------


since if there miss a match, s.training (from the outer join, not from the
table) will be null at least once, so the having clause won't be respected
and the employee will be removed as candidate for that position. We could
also have written the having clause as:

HAVING COUNT(r.training) = COUNT(s.training)



Vanderghast, Access MVP