Relation Ships with Dates

[Dennis the Safety Nerd]

I am trying to create a training data base. My tables include one for
employees, one for departments, one for training subject. I need to be able
to tell when each employee was last trained in a particular subject, What
subjects are required for employees in which departments, and when retraining
is due. Can anyone help me here? I have tried to build the table with each
subject as a yes/no field and, a training date. That way I can specify what
taining was done at a specific training. This seems to relate to the
employees. but how do I get it to relate to the requirements for each
department?

 

[KARL DEWEY]

Decide what your lowest common denominator for training must be - day, week,
month, etc.
In the Subject table have a number field indicating reoccurrence -
0 - Onetime
1 - One week or one month based on your overall training requirements.
4 - ...

Build a junction table (employee to subject). There will be a one-to-many
relations employee to junction and one-to-many relations subject to junction.
the junction table will have last trained date.

Use a query of DateAdd("X",[reoccurrence], [last trained date] < Date()

"X" is your lowest common demoniator (week "w", month "m", etc).
This adds the number of 'weeks' to the last trained. If it comes up less
than today then training is due. Use projections - 30-day projections would
be --
DateAdd("X",[reoccurrence], [last trained date] < Date() - 30

 

[Tim Ferguson]

I am trying to create a training data base. My tables include one for
employees, one for departments, one for training subject.

I need to
be able to tell when each employee was last trained in a particular
subject,

Okay: it's a straightforward many-to-many relationshipo between subjects
and employees. You'll need a table called TrainingReceived with fields
like Employee, SubjectName, DateCompleted, etc. If an employee can take a
course just once, the PK would probably be (EmployeeID, SubjectName), but
there can be duplicates then it would be (EmployeeID, SubjectName,
DateCompleted). You might also want fields in this table like
GradeAchieved, CourseFundedBy, DueForRepeatByDate etc etc.

What subjects are required for employees in which
departments, and when retraining is due.

Again, a many-to-many relationship between Departments and Subjects means
a table called SubjectsRequired and fields like DepartmentCode and
SubjectName, then optionally TimeToRepeat, MandatoryFundingBy and so on.
If the cost for a course varies by department, then that would be a field
in this table too.

You can create a query identify which employees are missing particular
courses, or which departments are completely covered -- too complex to do
here off the top of my head, but pretty feasible.

Hope that helps


Tim F