Solved Data Structure for Employee Training Record

[nashaz]

Hi again

As some of you already know I am working on building an employee training record db. I have become stuck at one issue in this db which I believe is down to how I have structured my data. The main functionality that I am looking out of it is to keep record of trainings employees have done, when they did it, and when does it expire. The trainings have to be classified as general and mandatory. Which training is mandatory depends on what role(s) an employee hold. Mandatory training for some roles may be offered as general training to other employees even if they do not hold that role e.g., first aid training may be mandatory for Health and Safety Officer, but may also be provided to a few other employees even if they are not Health and Safety Manager.

The way I have gone about structuring the db can be seen in detail in the cut down db I am attaching. In summary, I have a junction table between employees and courses which stores all trainings done by all employees, regardless of mandatory or general. I then have other junction tables to:

• establish link between roles and courses - to define which courses are mandatory for what roles
• establish link between roles and employees - to define which employees hold which roles

On my main employee form, I have put roles, generic training and mandatory training as subforms. The real issue is this: I want to show on the mandatory subform all the trainings from rolesxcourses table, and for each, display when they expire. If a training from rolexcourse table has not been delivered, ExpiresOn field remains blank and highlights in light red (via conditional formatting). Lastly, if a mandatory training has been delivered on more than one occassions, than I would like to show the most recent one in the mandatory training subform. But because all the delivered trainings are stored in EmployeeXCourse table which does not distinguish between mandatory and general training, I cannot separate the records. And the reason why I have made a single junction table for ALL trainings is to avoid duplication.

The closes I have gotten is by using Mandatory2Q query in the db. But that does not show all the trainings.

Thanks for your time and help, in advance!

 

[plog]

You've posted this in forms, but it sounds like a data issue, so let's just talk data and not confuse things by adding forms into it. You need a query that shows some data, correct?

My suggestion is to demonstrate your issue with data. Give us starting data and based on it show us what data that query should return. You've provided a sample database so that's the starting data. Using the data in there, show us the data you expect this query to return. Either mock it up in Excel or just write it in the post like so:

Field1Name, Field2Name, Field3Name...
Sally, 26, 1/1/2023
Dave, 38, 2/2/2022
...

Show us the data you expect to end up with when you feed in the data in that database you posted.

 

[MajP]

Is this correct?
qryDueMandatoryTraining qryDueMandatoryTraining

EmployeeID	LastName	Position	CourseName	ValidFor	Completed	Due
14​	Chapman	Health and Safety Manager	B1 Counterbalance Up To 5000kg	1825​	3/2/2023​	2/29/2028​
14​	Chapman	Test Site Operations Manager	Safe Handling of Industrial Gases	36500​	​	​
14​	Chapman	Test Site Operations Manager	Thorough Examination of Lifting Equipment Accessories	222​	​	​
17​	McNeill	Health and Safety Manager	B1 Counterbalance Up To 5000kg	1825​	3/2/2023​	2/29/2028​
18​	Marlow	Head of Engineering	Portable Appliance Testing	1095​	3/4/2023​	3/3/2026​

 

[nashaz]

You've posted this in forms, but it sounds like a data issue, so let's just talk data and not confuse things by adding forms into it. You need a query that shows some data, correct?

My suggestion is to demonstrate your issue with data. Give us starting data and based on it show us what data that query should return. You've provided a sample database so that's the starting data. Using the data in there, show us the data you expect this query to return. Either mock it up in Excel or just write it in the post like so:

Field1Name, Field2Name, Field3Name...
Sally, 26, 1/1/2023
Dave, 38, 2/2/2022
...

Show us the data you expect to end up with when you feed in the data in that database you posted.

Based on the actual data in the attached db, I would like to show, for each employee, mandatory trainings as follows:

EmployeeID	CourseID	CertificationDate	ValidFor	ExpiresOn
14	15	02/03/2023	1825	29/02/2028
14	36		36500
14	30		222
17	15		1825
18	37	04/03/2023	1095	03/03/2026

Note: the first record, i.e., EmployeeID 14 and CourseID 15, there already exists another training in record which has expired.

Cheers

 

[nashaz]

Is this correct?
qryDueMandatoryTraining qryDueMandatoryTraining

EmployeeID	LastName	Position	CourseName	ValidFor	Completed	Due
14​	Chapman	Health and Safety Manager	B1 Counterbalance Up To 5000kg	1825​	3/2/2023​	2/29/2028​
14​	Chapman	Test Site Operations Manager	Safe Handling of Industrial Gases	36500​	​	​
14​	Chapman	Test Site Operations Manager	Thorough Examination of Lifting Equipment Accessories	222​	​	​
17​	McNeill	Health and Safety Manager	B1 Counterbalance Up To 5000kg	1825​	3/2/2023​	2/29/2028​
18​	Marlow	Head of Engineering	Portable Appliance Testing	1095​	3/4/2023​	3/3/2026​

Very interesting and almost there A few points on the queries you very kindly setup in my db:

• qryDueMandatoryTraining shows a false record i.e., 2nd from bottom. If you look in EmployeeXCourse_JT McNeill hasn't yet completed the training B1 Counterbalance. The completed date access shows here is the same as that of Chapman, B1 Counterbalance training. Barring that, this is exactly what I needed
• When I try and setup the queries the way you have, I do not get the same results. For example, when I set my Mandatory2Q in the same way as you have setup qryRecentCompletedMandatory, query only displays mandatory trainings which have a CertificationDate
  
  
• As you can also see from qryRecentCompletedMandatory, there is a blank record, which I have figured out is because there is a role assigned to an employee but there are no trainings associated with that role. However, that empty record does go away in qryDueMandatoryTraining

Cheers.

 

[arnelgp]

try query2.

 

[nashaz]

try query2.

Thanks for your response. Query1 does not take into account a role assigned to more than one employees, which in this case is PositionID 48 assigned to EmployeeIDs 17 and 14.

 

[arnelgp]

how about this:

 

[nashaz]

how about this:

Exactly what I needed!!! Much obliged, arnelgp!

 

[Thomsie]

Hi again

As some of you already know I am working on building an employee training record db. I have become stuck at one issue in this db which I believe is down to how I have structured my data. The main functionality that I am looking out of it is to keep record of trainings employees have done, when they did it, and when does it expire. The trainings have to be classified as general and mandatory. Which training is mandatory depends on what role(s) an employee hold. Mandatory training for some roles may be offered as general training to other employees even if they do not hold that role e.g., first aid training may be mandatory for Health and Safety Officer, but may also be provided to a few other employees even if they are not Health and Safety Manager.

The way I have gone about structuring the db can be seen in detail in the cut down db I am attaching. In summary, I have a junction table between employees and courses which stores all trainings done by all employees, regardless of mandatory or general. I then have other junction tables to:

• establish link between roles and courses - to define which courses are mandatory for what roles
• establish link between roles and employees - to define which employees hold which roles

On my main employee form, I have put roles, generic training and mandatory training as subforms. The real issue is this: I want to show on the mandatory subform all the trainings from rolesxcourses table, and for each, display when they expire. If a training from rolexcourse table has not been delivered, ExpiresOn field remains blank and highlights in light red (via conditional formatting). Lastly, if a mandatory training has been delivered on more than one occassions, than I would like to show the most recent one in the mandatory training subform. But because all the delivered trainings are stored in EmployeeXCourse table which does not distinguish between mandatory and general training, I cannot separate the records. And the reason why I have made a single junction table for ALL trainings is to avoid duplication.

The closes I have gotten is by using Mandatory2Q query in the db. But that does not show all the trainings.

Thanks for your time and help, in advance!

Hi, I'm really interested in how you solved this as I have an almost identical database I'm working on. Do you have your X tables updating from somewhere because it seems otherwise you would have to maintain these separately. Do you have an update to your database that I may be able to have a look at? Really appreciate any help here. Thanks,

 

[nashaz]

Hi, I'm really interested in how you solved this as I have an almost identical database I'm working on. Do you have your X tables updating from somewhere because it seems otherwise you would have to maintain these separately. Do you have an update to your database that I may be able to have a look at? Really appreciate any help here. Thanks,

Hi
Please look at the db file in post #8 by arnelgp. That is what I implemented. If you still have further questions, let me know. Only reason I am saying this is that my database has evolved very much from back then. But I am sure I've used the technique in my new database somewhere.

 

[Thomsie]

Hi
Please look at the db file in post #8 by arnelgp. That is what I implemented. If you still have further questions, let me know. Only reason I am saying this is that my database has evolved very much from back then. But I am sure I've used the technique in my new database somewhere.

Hi, thanks so much for your response. I've downloaded that file and will have a look tomorrow (it's 10.30pm here now). Appreciate your help.

 

[Thomsie]

Hi, thanks so much for your response. I've downloaded that file and will have a look tomorrow (it's 10.30pm here now). Appreciate your help.

I've had a look at the database. The bit I was (and still am) grappling with is around the Junction tables. I get the setup and have set up one of them to link two tables - Position Description and Training Courses. I have an Excel spreadsheet with what courses apply to what positions so manually populated the Junction table. What I still don't get is how is that Junction table maintained if, say, a new training course or a new position description is added. Would love any help here. Thanks.

 

[Gasman]

You would have a form for that junction table.
I would have combos for the controls that hold the course/position whatever. You populate those combos from respective tables.

Here is a form from my very first Access DB. It is a form for a table called links.
All that table does is link to four other tables, Date,Ship,Rank and Crew.

I made a rookie mistake using Table Lookups, but I do understand what they are and how they work.

This is the table with out Lookups.

 

[Thomsie]

You would have a form for that junction table.
I would have combos for the controls that hold the course/position whatever. You populate those combos from respective tables.

Here is a form from my very first Access DB. It is a form for a table called links.
All that table does is link to four other tables, Date,Ship,Rank and Crew.

View attachment 115893
I made a rookie mistake using Table Lookups, but I do understand what they are and how they work.

This is the table with out Lookups.
View attachment 115894

OK thanks for that. I'll have a play. But what you are saying I think is that all 3 tables need to be populated almost manually. So, if I added another position description to the position table, I would then need to go to the form to add the same position to the junction table. I guess in reality the more common scenario where this would happen would be when I do the EmployeeXCourse table. It's more likely that that will be updated more often because new employees, different courses they do, etc.

 

[Thomsie]

OK thanks for that. I'll have a play. But what you are saying I think is that all 3 tables need to be populated almost manually. So, if I added another position description to the position table, I would then need to go to the form to add the same position to the junction table. I guess in reality the more common scenario where this would happen would be when I do the EmployeeXCourse table. It's more likely that that will be updated more often because new employees, different courses they do, etc.

Sorry just to confirm then. The underlying table for your form is the junction table and each of the combo boxes get their data from the respective tables. It would be nice to be able to use a query which runs automatically after you make a change/addition to one of the main tables.

 

[Gasman]

You have to take into account, I am a dabbler, not a developer in Access.
That is why I have table lookups in that DB. I do not use them anymore, but the DB is complete, so I am not going to muck around with it.

Each of my combos had code for NotInList, which would take me to the respective form for that table.
I entered manually over 22k entries using this method.
You will see a checkbox whose label is 'Copy From Previous Record'. With that I was able to copy the date and ship, and then just needed to add a new rank and select a crew member. If they had already been entered, then they appeared in the combo, and if not yet in, then they were added at that time. Slowly I got all the crew in the DB. They were the greatest number. Dates were only 53, ships 60 and Rank 154. Crew were 5206 and I thought I worked for a small shipping company at the time.

In your situation, I would add the new course in the course table.
Then it is available to your junction form. Now if you had to apply that to say 30 employees, then you could have a form that you tick each employee involved and then run some sql that would add records for that course for those employees.
You would not need to add them one by one. Of course if it is only one or two at a time, I would do it manually, but if you automate as I have suggested, then it could do 1 to as many employees as you have.

I was keying in everything manually from paper records.
This DB then is the basis of the website https://www.bibby-gazette.co.uk/ using pdf files that are reports from Access.

 

[Thomsie]

Great thanks so much for all your help. I'm not a developer either but have a lot of experience in Access/Excel/Powerpoint and Word. I just finished an invoicing database in Access for my son (who is a food safety consultant) as he was doing everything manually and/or in Excel. He loves it and says it has saved him heaps of time. My problem with this one (which I am also doing for him) was how to deal with the many to many relationships between tables and that's when I learned about Junction tables. So - something else I've learned today. Thanks again.

 

[Gasman]

It would be nice to be able to use a query which runs automatically after you make a change/addition to one of the main tables.

If you use Autonumbers (which you should), then amending the description of anything will not matter.
That links table of mine is just all autonumbers. I knew that much, even back then.

Say you get a new employee. They have to complete 3 courses when they join. They then complete other courses at set periods. You could automate that to add those courses and employee to the junction table automatically when a new employee is added and when a new course is due. The sky is your limit.

 

[Pat Hartman]

I would then need to go to the form to add the same position to the junction table

No, you only populate the junction table when you USE the new value. A junction table connects the definition tables.

It would be nice to be able to use a query which runs automatically after you make a change/addition to one of the main tables.

You NEVER automatically populate junction tables.

When you add a new required course, you then manually update ONLY the job titles that are relevant. Then, you can find the active people with the job titles that changed and run an append query to add the new courses to that junction table if that is what your workflow requires.