Employee Training database class assignment

[robina]

Hi,
My dbase stores employee info and classes they are required to take and the dates that they took them and to show alerts when classes are over due.

My problem is I want classes to be automatically assigned to an employee by the [job_title] field. I also need to assign classes that fall under the [All] category. Meaning all employees must take it. I have an employee table that has the following fields:
(PK)[Emp_ID] [Last_Name] [Shift] [DOH] [Dept] [Job_Title] [Supervisor]

The "Class_Catalog" table has the following fields:
(PK)[Class_ID] [Job_Title] [Class_Name] [Class_Date] [Recurrence]

And a "Classes_taken" table:
(PK)[Taken_ID] [Emp_ID] [Class_ID] [Date_Taken]

Right now required classes are input on an individual basis in a data entry form for each employee as needed. I want to keep this capability, but I now need to make the Job_Title auto-assign classes. My first thought was to make up tables for each job title but I don't know where to go from there.

Thank you so much

 

[speakers_86]

Why not just make another junction table?

tblClassesJobs
ClassesJobsID
ClassID
JobID (or in your case Job_Title)

 

[robina]

I'm liking that logic. Where I lose it is how would I then get all the classes for a particular JobID to be "assigned" to an employee with that job ID and to then be able to have that job pulled up on my form and put in a "Completed_Date" and then be able to create reports from the data? I'm getting confused as to where to store it. Currently I store the classes an employee has been assigned in the "Classes_taken" table and it includes a [Date_Taken] field that is blank until the class it taken.

 

[speakers_86]

The above table would have to be a reference only. There would need to be another table to record

ID
EmployeeID
ClassID
CompletionID

When an employee is created, or their job id changes, use an append query to get the values from the reference table (the table containing required classes for each job) and write those values to the table described here. You will probably have the table listed here as a sub form in your employee form to manually enter records.

edit-Make sure your append query utilizes the No Matches query. You can see how to build that in the query wizard. Also, consider handling classes that are expired, or due for retraining.

 

[robina]

Let's see if I understand:
I could use this table:
tblClassesJobs
ClassesJobsID
ClassID
JobID (or in your case Job_Title)

As a junction table between the Class Catalog tbl and the Classes_taken tbl (which is where I'll store assigned and completed classes by Emp_ID)

Is that it?