Originally Posted by
Oblio
I have attached an Access 2016 64 bit db sample.
The goal is to limit the Role an instructor can play in a Course based on the their Qualification Set.
All of the Qualifications Set Roles have been pre-populated in the jt_Qualification Sets_Roles many to many relationship. Please note it is only the NC and RC roles we are interested in.
So every correct Qualification set and related role are set in stone in the above junction table.
You will also note that a Course Type also limits the Qualification set in jt_Course_Types_Qualification_Sets, as a Course Type can have many Qualification Sets and a Qualification Set can have many Course Types.
So, an Instructor's qualification set (they can only have one qualification set at a time) is related to the type of course they can teach through their Qualifications set and what role they can play in a course.
An instructor with the Qualification Set 1 or 4 can only teach a NC Course as they only have an NC Qualification Set. Note that EVERY Instructor can teach the NC course type. The rest of the Qualification Sets can teach either the NC or RC course types and related Roles.
So, in documenting a course on a form, I need to add the Course ID, Instructor_ID and Roles they played in the course. An Instructor can have many Courses and a Course can have many Instructors.
Note: Instructors can only play 1 of the Instructor role types AND Examiner Role Types in each course. For example, an Instructor may not play the Role 1: NC Lead Instructor and 2: NC Instructor, but he can have 1 Instructor Role and Examiner role per course. In the example here, he could be 1NV:Lead Instructor AND 1:NC Lead Examiner.
I hope I have not made this too confusing to follow...and I hope someone can help me from going around in circles :/