How to limit a selection in a many to many relationship based on multiple mant to many relationships

[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 :/

[orange]

In your Roles table what do the 2,3 and 4 mean in role_names? I realize the IDs are distinct, but nothing to distinguish role_name?? Curious.

Roles_ID	Role_Name
1	1: NC Lead Instructor
2	2: NC Instructor
3	3: NC Instructor
4	4: NC Instructor

Do you have a Regions table?

Just curious about this statement

So every correct Qualification set and related role are set in stone in the above junction table.

"they can only have one qualification set at a time" do you need some sort of time component to ensure this?

[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 :/

A maximum of 4 Instructors and Examiners per course is allowed. The numbers are simply ordinals to keep them in order as they are listed on a report. The Lead Instructor and Examiner are the most important.

[Oblio]

Sorry, I do have a Regions table....just didn't include it to help keep focus. As far as the qualification sets are concerned we do not require a history of them, however if you think a many to many relationship with a date is a good idea I could be persuaded to go in that direction.

What I am trying to do is insure when I have a course that my instructor role is limited by the course type as well as the qualification set they possess, and only they can only play one role type per course. For example, they cannot be Instructor 1 and 2. Also, they cannot teach an RC course if they have qualification set 1 which is NC only.

[NTC]

Fundamentally your question is about cascading comboboxes. When you say 'select' - then one presumes the use of a combobox or listbox control on a form. Typically it is a combobox.

Part of the issue is implementation - as to how you want the user interface / experience to be.

But in any case the combo/list box is itself based on a query, and a query can have 'criteria' calling from a form field such as another combobox (which is why it is called cascading)

So in your form, as the user, consider what you want them to select 1st and call this field/value S1. Then in their next selection you use a query that relies on S1 as its criteria to limit the data set choice for S2. So on and so forth.