Question Access Training Database (1 Viewer)

AGNewton5

Registered User.
Local time
Today, 13:33
Joined
Aug 20, 2019
Messages
28
Hi,

I am attempting to build a database that will track training of employees in my department. I am struggling with some things in the creation part of it. My struggle is that I am needing to have about 70 different training courses (each course would have a Revision date and revision #) that will need to be accessible from 8 different Job Positions. I would also need to have the training completion date be impacted by revisions. A lot of the jobs have similar training, but the more complex jobs require some additional training as well. I would like to have forms that would be specific to the job position, and would want to store a scanned version of the training in PDF form with each record. I know this is a lot and I am very new to Access, but any help in getting started would be greatly appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:33
Joined
Oct 29, 2018
Messages
21,358
Hi. You need to start designing your table structure. Once you have something started, you can post it here for a quick review.
 

AGNewton5

Registered User.
Local time
Today, 13:33
Joined
Aug 20, 2019
Messages
28
Basically I will have the following major categories:

EmployeeTable
EmployeeID
FirstName
LastName
Position
Shift
Active

TrainingTable
TrainingID
TrainingDetails
TrainingRevisionDate
TrainingRevision#

My confusion at this point would be linking this data so that each position automatically populates the correct training path to be qualified. I have over 100 different training courses and each course could be needed for several positions. These courses also have to have completion dates (for when the employee completes the training- this would need to have some sort of trigger to flag employees that have completion dates prior to the revision) that link to the employee. I really would like to learn more about databases and how to make these function. Any help would be greatly appreciated.
 

GinaWhipp

AWF VIP
Local time
Today, 14:33
Joined
Jun 21, 2011
Messages
5,901
Well, I posted a link, did you see that? Based on what you posted seems like it might help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:33
Joined
Oct 29, 2018
Messages
21,358
Basically I will have the following major categories:
Hi. You're going to need more tables than that. Please take a look at the link Gina provided and post any questions you have.
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:33
Joined
Jan 23, 2006
Messages
15,364
Gina has offered a more complete model, but I'm attaching one that may help put a few things into context.

Build a model, some test scenarios and data and vet your model before getting too deeply into physical MS Access.

Good luck.
 

Attachments

  • EmployeePositionTraining.jpg
    EmployeePositionTraining.jpg
    40.9 KB · Views: 121

AGNewton5

Registered User.
Local time
Today, 13:33
Joined
Aug 20, 2019
Messages
28
jdraw,

I feel that your description is nearly exactly what I am trying to do. I guess I am still trying to get the basics down for how to structure everything and then link the needed parts together. On your model, would it be possible to have a revision of training that would use the revision date and revision # that could help me track when an employee is needing an update training? I know it is just a sketch, but I am trying to figure out where I would structure that in order to achieve what I am looking to do.

Thank you in advance.
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:33
Joined
Jan 23, 2006
Messages
15,364
I suppose anything is possible. I don't understand the requirement in any detail.
It seems you are possibly dealing with Training Course syllabus or some detail for revisions to Courses.
Another aspect of what you may be facing is changes/revisions to Course Session Dates. That is, Courses are the Subject matter, they are presented in different Sessions/Rooms/Times/Dates/Teacher depending on how involved you may be.

I presented an overview model to show Training Required by Position or Taken / Scheduled by Employee who may Occupy a Position.

If you write a detailed description of what you are trying to support with a database, then I'm sure you'll get lots of focused responses.

You may find models at Barry Williams' site that suit your needs (at least in part).

Gina has provided a detailed model that should give you some insight.

Good luck.

http://www.databaseanswers.org/data_models/student_rosters/index.htm
 

Mark_

Longboard on the internet
Local time
Today, 11:33
Joined
Sep 12, 2017
Messages
2,111
jdraw,

I feel that your description is nearly exactly what I am trying to do. I guess I am still trying to get the basics down for how to structure everything and then link the needed parts together. On your model, would it be possible to have a revision of training that would use the revision date and revision # that could help me track when an employee is needing an update training? I know it is just a sketch, but I am trying to figure out where I would structure that in order to achieve what I am looking to do.

Thank you in advance.

To help clarify, Does the training fall into one of the following?

1) A given course is only good for a set duration (normally 1 year or 2 years) and needs to be redone afterward?
2) A given course is good until a new "revision" comes out, then EVERYONE needs to take the new "revision"?
3) Both 1 and 2 above?
 

AGNewton5

Registered User.
Local time
Today, 13:33
Joined
Aug 20, 2019
Messages
28
I hope this works, hopefully I have attached an idea of the design using your structure.


design.JPG
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:33
Joined
Jan 23, 2006
Messages
15,364
Please tell us more about RevisionDate and Revision number. Are you suggesting that for some courses the content is so significantly revised that an Employee must take the revised course id addition to a similarly titled course that he/she may have alreay aken successfully???
 

AGNewton5

Registered User.
Local time
Today, 13:33
Joined
Aug 20, 2019
Messages
28
The Revision Date and Revision # are done when work processes change to ensure that the employee understands the slightly modified tasks that they need to complete. The overall TrainingID will stay the same, but all employees that have taken the course previously will need to retake it to be compliant. Basically the date completed would be the most important part of this. Once the course would be modified, the date of modification would need to trigger all employees that need retraining. I hope I didn't over complicate this.

thank you in advance!
 
Last edited:

AGNewton5

Registered User.
Local time
Today, 13:33
Joined
Aug 20, 2019
Messages
28
To help clarify, Does the training fall into one of the following?

1) A given course is only good for a set duration (normally 1 year or 2 years) and needs to be redone afterward?
2) A given course is good until a new "revision" comes out, then EVERYONE needs to take the new "revision"?
3) Both 1 and 2 above?

Mark, #3 is correct. Training is normally good for 5 years. Revisions would make that timeline shorter. I supervise a Manufacturing plant and want a better system to help keep control of my employees training.

Thank you in advance!
 

Mark_

Longboard on the internet
Local time
Today, 11:33
Joined
Sep 12, 2017
Messages
2,111
The Revision Date and Revision # are done when work processes change to ensure that the employee understands the slightly modified tasks that they need to complete. The overall TrainingID will stay the same, but all employees that have taken the course previously will need to retake it to be compliant. Basically the date completed would be the most important part of this. Once the course would be modified, the date of modification would need to trigger all employees that need retraining. I hope I didn't over complicate this.

thank you in advance!

I put that in as bold and red to make sure were very clear;

Employee takes "Widget Waxing" V1.
When "Widget Waxing" V2 comes out, they need to take the course all over again.

This means that you have a "Training Category" of "Widget Waxing" and a "Training Course" of "Widget Waxing V1" or "Widget Waxing V2". Each would get its own ID and you'd look for that.

When you try to match up what is needed for a given position, you look to the category, but when you look for "What is required on date" you look at the course and its start date.
 

AGNewton5

Registered User.
Local time
Today, 13:33
Joined
Aug 20, 2019
Messages
28
That would be correct. My problem with that would be during data entry. I wouldn't want to be constantly updating forms to the current version of the training if possible. There are about 140 different training courses in my department and I would like this database to be a low maintenance way of looking up adding new training to an employees file.
 

AGNewton5

Registered User.
Local time
Today, 13:33
Joined
Aug 20, 2019
Messages
28
The examples you gave are correct. My concern is that I would need to modify a lot of things every time one of the 140 different training courses is updated. Like forms and other points of entry. Or is there a way to ease that pain. My purpose for creating this is to make training audits a little less painful as well as making adding new employees and updating current employees a lot easier.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:33
Joined
Feb 19, 2002
Messages
42,971
I believe we've already determined that your courses are held in two tables. The parent table that identifies the course and the child identifies the version. The version is what is linked to the person table via a junction table.

You could identify the "current" by using the version number or date or perhaps a flag on the version record that indicates "this is the current version". The process where you set "this is the current version" would need to unset any previous records with this flag. I would need to spend more time thinking about how the queries would work to decide which of the three methods (or possibly something different entirely) would work the best. In fact, a fourth option would be to break all the rules and store the ID of the current version record in the parent record. This on the surface seems to violate normalization rules but I'm not sure and it WILL make queries easier and will be easier to enforce when updating since one and only one value can be stored in the parent record.

One other thing no one has mentioned yet is that you would maintain a list of required courses by job position and this would be used to enforce requirements on employees. The required courses list would not include version. Version would always be assumed to be the "current" version whatever that is. That way, you would never have to change the required courses table when you add new versions of the courses.
 

AGNewton5

Registered User.
Local time
Today, 13:33
Joined
Aug 20, 2019
Messages
28
I believe we've already determined that your courses are held in two tables. The parent table that identifies the course and the child identifies the version. The version is what is linked to the person table via a junction table.

You could identify the "current" by using the version number or date or perhaps a flag on the version record that indicates "this is the current version". The process where you set "this is the current version" would need to unset any previous records with this flag. I would need to spend more time thinking about how the queries would work to decide which of the three methods (or possibly something different entirely) would work the best. In fact, a fourth option would be to break all the rules and store the ID of the current version record in the parent record. This on the surface seems to violate normalization rules but I'm not sure and it WILL make queries easier and will be easier to enforce when updating since one and only one value can be stored in the parent record.

One other thing no one has mentioned yet is that you would maintain a list of required courses by job position and this would be used to enforce requirements on employees. The required courses list would not include version. Version would always be assumed to be the "current" version whatever that is. That way, you would never have to change the required courses table when you add new versions of the courses.

I think that using the date for the revision would work. It would make distinguishing whether the employee is needing retraining easier. I am very new at Access and have a lot to learn to make these things come together. I have been reading a couple of books, but the newness is still very real.
 

Mark_

Longboard on the internet
Local time
Today, 11:33
Joined
Sep 12, 2017
Messages
2,111
For myself, in the child "Version" table you can have an "As of" date that defaults to Date(). This means you can find the latest version by simply looking for the newest "Version" record for a given course.
 

Users who are viewing this thread

Top Bottom