Seeing Incomplete Task

[E.Q.]

I've been asked about tracking training for new employees. We have a list of
training objectives for each area. I'd like to track when an employee
received training for each objective; I don't anticipate a problem with that
part of the application. But I'd also like to generate a report showing any
training objectives that a given employee has not been shown (or similarly,
has not been certified by the supervisor as having mastered - another report).
For example, John Doe is to train in Primary, Secondary, and Tertiary. (We
actually have dozens of objectives.) Say I've tracked that he's been trained
and certified in Primary, trained in secondary (not certified) but has yet to
see tertiary. I'd like to have a report showing where he has and hasn't been
trained or certified.
I just got the e-mail requesting I look into this. I'm just starting to
consider design and was wondering how to best include the "needs training"
option.
Any tips will be appreciated.
Peace.
EQC

 

[KARL DEWEY]

I suggest the following tables -
Employee --
EmpID - autonumber - primary key
LName
FName
MI
Suffix - JR, SR, III, etc
Sex
DOB - DateTime
Etc.

Training ---
TrainID - autonumber - primary key
Name - include each phase and certification separately
Required - what requires - OSHA, HR, Safety, etc.
Reocurr - integer - interval months
etc

TngRecord ---
TngRcdID - autonumber - primary key
TrainID - number - integer - foreign key
EmpID - number - integer - foreign key
TngDate - DateTime - date training phase/certificatification completed
Remarks - memo

Create a TngRecord for each employee/course combination that is required.

Use query to pull last TngRecord using interval criteria and DateAdd
function to generate next training requirement.

 

[Ken Snell \(MVP\)]

Assuming that you have a Goals table for each employee, you would want to
have a field in your TrainingTaken table that identifies the goal that is
satisfied by a training class. You then could write a query that looks to
find goals that are not "in" the TrainingTaken table.

(A more advanced design would be used if one training class satisfies more
than one goal. In that case, you need a new table -- TrainingClassGoals --
that links a training class to each goal met by that class -- one record for
each unique combination. You then would use this table in the above query.)

For us to give you more specific suggestions, we'll need to know more about
your table structure.

 

[Ken Snell \(MVP\)]

Badly worded...

Change
Assuming that you have a Goals table for each employee,

To
Assuming that you have a Goals table where you store the goals for each
employee,

 

[John W. Vinson]

I've been asked about tracking training for new employees. We have a list of
training objectives for each area. I'd like to track when an employee
received training for each objective; I don't anticipate a problem with that
part of the application. But I'd also like to generate a report showing any
training objectives that a given employee has not been shown (or similarly,
has not been certified by the supervisor as having mastered - another report).
For example, John Doe is to train in Primary, Secondary, and Tertiary. (We
actually have dozens of objectives.) Say I've tracked that he's been trained
and certified in Primary, trained in secondary (not certified) but has yet to
see tertiary. I'd like to have a report showing where he has and hasn't been
trained or certified.
I just got the e-mail requesting I look into this. I'm just starting to
consider design and was wondering how to best include the "needs training"
option.
Any tips will be appreciated.
Peace.
EQC

You don't describe your table structure - maybe that's what you're asking!

I'd suggest (at least) three tables: Employees, Objectives, and Training. The
Objectives table would contain one record for each objective which an employee
is to be trained. The Training table would resolve the many to many
relationship between these two tables - it would have fields for the
EmployeeID, the Objective, and other fields pertaining to *this* employees
training with *this* objective. These might include DateTrained,
DateCertified, a comments field, a satisfactory/unsatisfactory yes/no field,
etc.

You could then use the "Unmatched Query Wizard" to find all employees who have
NOT had training on a particular objective.

John W. Vinson [MVP]

 

[E.Q.]

I apologize for putting the cart before the horse on this a bit; I posted my
question as I was scribbling db ideas on a legal pad...
I thank you for your comments so far. You've pointed out things I hadn't
considered. I'll share the tables I came up with, but I particularly like
Ken's idea of a goals table (as you'll see I don't have anything that quite
corresponds).
Here's my initial table set up. (BTW - this training is a hands-on training;
the goal of the database would be to ensure that the field employees doing
the training cover all the bases with the transferred employees.)
tblEmployees:
chrEmpID (KEY using existing employee number)
chrLastName
chrFirstName
chrJobTitle

tblTracking:
lngTrackID (Key Autonumber)
chrEmpID (foreign)
dtmWorkDate
chrShift
chrRole
sngHoursWorked
chrTracked Comments
chrTrainerID (recursive to chrEmpID in tblEmployee)
chrSupervisor (ditto)

tblTask:
lngTaskID (Key Autonumber)
lngTrackID (foreign)
lngObjID(foreign)
blnTrained (A note if topic covered)
blnCertified (To indicate supervisor has checked employee progress)
chrTaskComment

tblTraningObjectives:
lngObjID (Autonumber KEY)
chrPlantArea
chrObjectiveName
chrDescription

I also have a couple single field tables to provide the cbo options for
chrPlantArea and chrRole respectively.

Other than the problem that led to my initial post (i.e., how to identify
those objectives that the trainee has yet to be trained in), I spotted
another potential problem. I basically designed the possibility to "certify"
completion of an objective each day when the tracking form is filled out. (I
built a form using tblTracking that contained a subform to populate
tblTasks).
As you can see I don't have a goals list for each employee. So based on the
responses so far, perhaps an addition table like:

tblEmployeeGoals:
lngEmpGoalID (autonumber KEY)
chrEmpID
lngObjID
blnCertified (and remove the field from tblTasks)
chrSupervisor (In this case to denote which of us passed the employee)
dtmDateCertified
chrEmpGoalComments

My concern with this approach regards how to populate this table. I've not
counted the list of objectives, but it's probably in the 60 - 75 range. We
have nine employees to enter the program. Would there be a way to automate
record creation (or will someone need to create these records individually)
One thing I need to add: we give trainees comprehensive tests at 3- and
6-months. I had thought I'd treat these as special training objectives and
tracking scores in chrTrackedComments or perhaps now in chrEmpgoalComments.
Once again, thank you for your responses.
EQC

 

[Ken Snell \(MVP\)]

I apologize for putting the cart before the horse on this a bit; I posted
my
question as I was scribbling db ideas on a legal pad...
I thank you for your comments so far. You've pointed out things I hadn't
considered. I'll share the tables I came up with, but I particularly like
Ken's idea of a goals table (as you'll see I don't have anything that
quite
corresponds).
Here's my initial table set up. (BTW - this training is a hands-on
training;
the goal of the database would be to ensure that the field employees doing
the training cover all the bases with the transferred employees.)
tblEmployees:
chrEmpID (KEY using existing employee number)
chrLastName
chrFirstName
chrJobTitle

tblTracking:
lngTrackID (Key Autonumber)
chrEmpID (foreign)
dtmWorkDate
chrShift
chrRole
sngHoursWorked
chrTracked Comments
chrTrainerID (recursive to chrEmpID in tblEmployee)
chrSupervisor (ditto)

tblTask:
lngTaskID (Key Autonumber)
lngTrackID (foreign)
lngObjID(foreign)
blnTrained (A note if topic covered)
blnCertified (To indicate supervisor has checked employee progress)
chrTaskComment

tblTraningObjectives:
lngObjID (Autonumber KEY)
chrPlantArea
chrObjectiveName
chrDescription

I also have a couple single field tables to provide the cbo options for
chrPlantArea and chrRole respectively.

Other than the problem that led to my initial post (i.e., how to identify
those objectives that the trainee has yet to be trained in), I spotted
another potential problem. I basically designed the possibility to
"certify"
completion of an objective each day when the tracking form is filled out.
(I
built a form using tblTracking that contained a subform to populate
tblTasks).
As you can see I don't have a goals list for each employee. So based on
the
responses so far, perhaps an addition table like:

tblEmployeeGoals:
lngEmpGoalID (autonumber KEY)
chrEmpID
lngObjID
blnCertified (and remove the field from tblTasks)
chrSupervisor (In this case to denote which of us passed the employee)
dtmDateCertified
chrEmpGoalComments

My concern with this approach regards how to populate this table. I've not
counted the list of objectives, but it's probably in the 60 - 75 range.
We
have nine employees to enter the program. Would there be a way to automate
record creation (or will someone need to create these records
individually)
One thing I need to add: we give trainees comprehensive tests at 3- and
6-months. I had thought I'd treat these as special training objectives and
tracking scores in chrTrackedComments or perhaps now in
chrEmpgoalComments.
Once again, thank you for your responses.
EQC

With regard to "populate this table", do you mean to do this for the initial
setup of the database, or as an ongoing step in adding new data to the
database? it's possible to create append queries that can add data to a
table easily (much more easily than manually entering data), and then run
those queries. This can be done for either "initial setup" or "ongoing data
entry" situation. For the former, "quick and dirty" is ok; for the latter,
you would want to be include error validation, error recovery, forms to
allow easy "kickoff", etc.

Comprehensive tests could be considered goals, I think.