I'm trying to set up an employee training database.
I have an employee table, courses table, and training records table.
I would like to create a query based on several required courses being completed, and set the expiration date for the qualification based on the most conservative training date.
I'm not sure how to go about this, but I would like something like this where Qual_1 = Course A + Course B + Course C.
EmployeeTable
EmployeeID_PK FirstName LastName
JSmith James Smith
CoursesTable
CourseID_PK Validity
CourseA 12 months
CourseB 12 months
CourseC No Expiration
TrainingRecordsTable
EmployeeID_FK CourseID_FK Date Taken
JSmith Course_A 7/13/2019
JSmith Course_B 7/10/2019
JSmith Course_C 7/12/2019
Desired Query Results:
EmployeeID Qualification Expiration
JSmith Qual_1 7/10/2020
Do I need to create a separate qualifications table in order to perform this query? or add the qualification to the courses table? or use some kind of if statement in a query to get the results I'm looking for?
I have several qualifications that I will need to create, some require only one course and others require two or more courses to satisfy the qualification. Some courses have an annual retraining requirement and others are valid for lifetime of the employee like Course_C in my example.