Training Records (1 Viewer)

damian

Registered User.
Local time
Today, 19:07
Joined
Jun 27, 2004
Messages
87
I’m attempting to set up a database to record employee training. I’ve set up the autolookup query (pic attached) to help form a set of default training courses/procedures that are mandatory for each department – if Department A requires eg CPR, Fire Safety and Working at Height training, the relevant DefaultTrainingID’s can be selected and all details are auto filled from tblTrainingCourses. Furthermore, if a procedure is revised, the revision for that training only has to be changed once in the tblTrainingCourses table.

I want to move to the next stage whereby these default courses can be pulled in to an individual’s training record when they join eg Department A but retain the ability to edit these records so that eg John Doe’s Fire Safety training record will reflect completion dates, current revision that he has been trained against etc. There will also be occasions when individuals require training for responsibilities additional to their departmental mandatory requirements - so Environmental Awareness may have to be added to the other 3 courses for Department A for John Doe


Could anyone please point me in the right direction at this point - will an update or append query, triggered by an AfterUpdate event on a form after selecting the employee’s department, be the best/most simplistic way of achieving this? Would appreciate a little steer
 

Attachments

  • Untitled.png
    Untitled.png
    31.2 KB · Views: 83

Mihail

Registered User.
Local time
Today, 21:07
Joined
Jan 22, 2011
Messages
2,373
As far as I can understand from your description you need (at least) this:

tblTrainingCourses
ID_TrainingCours - AutoNumber (PK)
TrainingName
TrainingDescription etc

tblDepartments
ID_Department - AutoNumber (PK)
DepartmentName etc

tblEmployes
ID_Employe - AutoNumber (PK)
EmployeName etc

tblDepartmentsTrainings the necessary trainings for departments
ID_DepartmentTraining - AutoNumber (PK)
ID_Department - Number (FK on tblDepartments)
ID_TrainingCours - Number (FK on tblTrainingCourses)
etc

tblEmployesTrainings the trainings that an employe already learned
ID_EmployeTraining - AutoNumber (PK)
ID_Employe - Number (FK on tblEmployes)
ID_TrainingCours - Number (FK on tblTrainingCourses)
etc

Having this tables you can see (calculate)
1) If an employe already have necessary trainings for an certain department
2) What more trainings are necessary for an employe to fit to a certain department
3) What employes already fit to a certain department
4) To what department can join a certain employe based on his trainings
and more...

Is this what you are looking for ?
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:07
Joined
Jan 23, 2006
Messages
15,364
Here is a diagram that may be helpful to you. In this case, certain Positions required a certain Training. Employees who filled those Positions had to have, or had to take the Training required for the Position they occupy.

In effect there are 3 concepts involved.
1- A Position requires Training
2- An Employee takes/has taken Training
3- Does an Employee have the necessary Training for his/her Position?

Hope it's useful.
 

Attachments

  • EmployeesAndPositions_V0.jpg
    EmployeesAndPositions_V0.jpg
    66.2 KB · Views: 87

damian

Registered User.
Local time
Today, 19:07
Joined
Jun 27, 2004
Messages
87
Thanks for both your responses. I had already set up the attached tables /relationships (should have posted originally) and I think that it captures what you guys have advocated.

I had also set up the autolookup query (my initial post) so that if a course detail changed , it would be easy to edit so that all records for that course would automatically update. This would also facilitate inputting the departmental requirements as you simply have to select the 'Default Training ID' and all course details would autopopulate.

But I'm still struggling to fathom out if there is a way to set up a query that allows me to incorporate these course details into an employees training records and be able to edit eg the course revision number and all training records for that course reflects that change. Perhaps the autolookup query over complicates my goal?
 

Attachments

  • Capture.JPG
    Capture.JPG
    56.8 KB · Views: 81

spikepl

Eledittingent Beliped
Local time
Today, 20:07
Joined
Nov 3, 2010
Messages
6,144
I would split revisions into a separate table from a course. The reason is that departmental requirements specify a given course, irrespective of revision. Your current structure requires respecifying the requirement, each time a new revision is issued.

You should study jdraws' pic. Requirements are recorded in a junction table linking positions with courses.
 

Mihail

Registered User.
Local time
Today, 21:07
Joined
Jan 22, 2011
Messages
2,373
I don't wish to say that my approach is the single one or the best one.
Take a closer look to my tables and to jdraw's picture.
You will see that:

1) Only the PK from a table is used as FK in a child table. Not other fields, as you have between tblTrainingCategory and tblTrainingCourses.

2) We always use the same field name for a FK as for the PK.. This way we will know at any time from where (from what PK) the FK come from. Will be very useful when you will start do design queries.

3) It is a slightly difference between me and jdraw when we name the table and the PK in the same table. I use the plural for the table name (because it store many objects) and the singular for the PK name (because is attached to a single object from that table). jdraw use the singular for both.
BUT each one of us use the same "rule" : the PK name include the table name. Again this will be very useful later because when, we'll see a PK or a FK, we'll know from start the original table for that value.
 

damian

Registered User.
Local time
Today, 19:07
Joined
Jun 27, 2004
Messages
87
Many thanks to all 3 of you and apologies for not getting back sooner - work is taking over my life.

Have yet to sort queries but it's a lot clearer now that the foundations are in place. I tend to rely too much on VBA to mask any deficiencies in my database designs but this has made the task a lot easier.

Thanks again
Damian
 

Mihail

Registered User.
Local time
Today, 21:07
Joined
Jan 22, 2011
Messages
2,373
I tend to rely too much on VBA to mask any deficiencies in my database designs
From who ? From yourself ? Bad idea :)
 

Users who are viewing this thread

Top Bottom