Solved Data Structure for Employee Training Record (1 Viewer)

[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!