Question Access Training Database (1 Viewer)

Pat Hartman said:

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.

Click to expand...

AGNewton5 said:

If I were to not worry about the revision on the actual training document and just modify the date when a revision occurs, could I then create a conditional format (like in excel) that would highlight/ red flag any employee that needs retraining?

Thanks in advance!

Click to expand...

If I were to not worry about the revision on the actual training document

Click to expand...

Pat Hartman said:

That wasn't quite what I meant. What I meant was that when you were defining which courses were required, you would do it at the course level. However, when someone completed the training, you would log it as the specific version.

I don't know what prompts you to look for new training, maybe, once per year, you run a query that finds everyone who has any outdated courses and schedule them to take the new version. Maybe, whenever you add a new version, you trigger producing a list at that time and scheduling all people who are required to take that master course, to take the new version.

Click to expand...