Relation Ships with Dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dennis the Safety Nerd - 09 Aug 2005 20:58 GMT

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?

Reply to this Message

KARL DEWEY - 09 Aug 2005 23:13 GMT

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

> 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
[quoted text clipped - 5 lines]
> employees. but how do I get it to relate to the requirements for each
> department?

Reply to this Message

Tim Ferguson - 10 Aug 2005 17:21 GMT

"=?Utf-8?B?RGVubmlzIHRoZSBTYWZldHkgTmVyZA==?="
<DennistheSafetyNerd@discussions.microsoft.com> wrote in
news:EA0D63B9-0FE4-4BE1-8CED-0CF4588ECE58@microsoft.com:

> 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

Reply to this Message