Tables for tracking training - many to many relationships? 2

[jazminecat]

Hi, I am stuck, perhaps I just have FridayBrain. I have a table that lists all my employees - tblEmployees. I have to keep track of what training they have taken. I have a table TrainingTypes to list the most frequent types of training (i.e. first aid, etc). Not all employees take all training (some are computer users, aothers are manual laborers...).

I want to give my user a form, which will show all the standard training types, with a checkbox next to each to indicate if the employee has taken them
. If the checkbox is checked, I will make the date and and expiration visible. But I also need to be able to have a few slots for her to type in the names of other types of training not in the TrainingTypes Table.

And for some reason, this rather simple problem has me completely stymied. Do I create another table which has an id, the employee id, and the trainingtype id, to hold the yes/no for each and the dates and expiry? and if so, how do I populate a form without having the table populated? I thought of writing a little function to run when the checkbox gets checked, creating a new record in such a table. But...what happens if she checks one, and then realizes she needs to uncheck it. The record will have been created and now needs to be deleted. I can just see the mess that could make...

Have a good weekend, and thanks in advance for your help.

 

[willir]

jazminecat

Easy stuff first.

You have identified that you have a many-to-many relationship between employee and course. An employee can take many courses. A course can include many employees.

Assuming the basics...

tblEmployee
EmployeeID - pk
EmployeeLN - last name
EmployeeFN - first name
...etc

tblCourse
CourseCode - pk
CourseName
CertificateName
RecertifyPeriod - interger

...Something fairly simple...
tblEmployeeCourse
EmployeeID - foreign key to tblEmployee
CourseCode - foreign key to tblCourse
CourseMark
CompletionDate - date
Certificate - boolean, yes / no
ExpirationDate - date

Primary key = EmployeeID + CourseCode + CompletionDate

Discussion:
Having the EmployeeID + CourseCode is the typical approach. However, it sounds like you are in a situation where the employee may have to be re-certified. Consequently, the an employee may re-take the same course, so I added the CompletionDate as part of the primary key.

The expiration date will be useful if an employee has to recertify. It would use the tblCourse.RecertifyPeriod value to calculate a future date.

Course mark and Certificate yes/no just provide more useful information.

...Moving on to more advanced.
Your idea of "batching" course is excellent. All employees take a course on harassment but some will have to take a course on whmis and others on conflict of interest. Great idea.

Again, you have a many-to-many. An employee could belong to more than one group, and a group will include more than one employee.

tblTrainingGroup
TrainingGroupCode - pk
GroupName

tblCourseGroup
CourseCode - fk
TrainingGroupCode - fk
Mandatory - boolean, yes/no

primary key = CourseCode + TrainingGroupCode.

Discussion:
For each group, build a "profile" of what courses are mandatory or recommended for a group.

Ditto for employees...

tblEmployeeTrainingGroup
EmployeeID - fk
TrainingGroupCode - fk

Pimary key = EmployeeID + TrainingGroupCode

Discussion:
Assign training groups to an employee.

Works as follows...

[tt]
tblTrainingGroup
TrainingGroupCode GroupName
ALL All employees
MGR Managers
TECH Technical
SALES Sales
PRDT Production

tblCourse
CourseCode CourseName
HARASS Harassement awareness
WHMIS1 WHMIS level 1
CNFLT Conflict of interest
FAIDE First Aide

tblCourseGroup
CourseCode TrainingGroupCode Mandatory
HARASS ALL Yes
WHMIS1 TECH Yes
WHMIS1 SALES No
CNFLT MGR Yes
FAIDE PRDT Yes
FAIDE ALL No
[/tt]

I am not going to go through show how to assign employees -- I think you realize "how to".

Note that an employee who can belong to more than one group, can be in one group where training is mandatory and no mandatory in the other. For example, "Production", first aide is a "must", but only recommended for "All" employees. In this case, the employee who is in production must take the course.

Does this help?
Richard

 

[jazminecat]

This helps tremendously! Thanks Richard, you've answered at least 4 questions for me in this one post. I need to work out how to make a primary key by combining data from two other tables since I usually just use an autonumber field for the primary key, but that shouldn't be too difficult. Just need to get caffiene in me enough to get the brain started. I'm a little rusty since I've been away from programming for about 9 months, but I've built some really great applications in the past with more than a few bits of advice from this forum. Really appreciate folks like you here! thanks again, have a star!

 

[willir]

Compound index...
Open table in design mode
From the menu, View -> Indexes

Assign a name for the unique
Select the first field for the table
Underneath, in the second row, select the second field.
Got back to the first row, and set index property to primary. Note that there are other ways, but this Index window allows more flexibility for other situations you may run into.

Thanks for the star

Richard