Multifield Index Help Required

[Oblio1963]

Three tables: Courses, Instructors, and Roles.

Each course can have anywhere from 1 to 5 instructors, so a junction table works for that. However, we also need to say which instructor was the Lead Instructor, and each subsequent number is assigned for each additional instructor used up to a max of 5.

Sample Data:

CourseID InstructorID RoleID
1 1 1
1 2 2
First row translates to: Course 1, Instructor 1 "Jim Smith", was the Lead Instructor.
Second row: Course 1 had a second instructor "Tim Jones" the secondary instructor.

So, I need an index that allows a different course ID but insists that for each course the instructor and roles must be unique. Secondly, I want to enforce that the Roles are progressive... in other words, you have to choose role 1, then role 2, and that you cannot choose role 3 first and maybe no other roles...

I have tried different index combinations and Primary keys but I cannot seem to get it right... please help me :banghead:

 

[CJ_London]

suggest investigate using the 'DMax+1' concept for generating your role numbers

 

[jdraw]

Further to CJ's response, could you clarify your situation?
Often there are Courses and Instructors, and also CourseSession or Class.
For example, Math321 is a Course, John Smith and Jane Doe are Instructors.
And perhaps Math321 is presented in Room 200 at 3:00 PM Tuesday and Thursday(sessions) with Instructor John Smith, and Math321 is given at 10:00AM Wednesday and Friday(other sessions) in Yahoo Hall with instructor Jane Doe.

Just trying to understand your tables and relationships.

Good luck with your project.

 

[Oblio1963]

We have 2 course types: CFSC and CRFSC.

All we need to track is:

How many Instructors taught the course, What role number they were (Lead, secondary etc)

and what students were in that course. (it's a 10 hour course taught over a weekend).

Beyond my original question is I will also need to link the students exam marks and examiners and their roles.

We do not track where the courses take place, other than City, Country etc.

Does this help clarify things?

 

[stopher]

For the index problem you can apply two indexes. The first index will be based on the fields CourseID and InstructorID. The second index will be based on the fields CourseID and RoleID.

For applying the constraint that the roles must be in sequence you can implement this kind of constraint using SQL. But there are other things to consider. Suppose you have 3 instructors (roles 1,2,3). Then instructor 2 is no longer available and gets deleted. So then you end up with a gap.

 

[jdraw]

These terms may be relevant to you, but are unknown to us - CFSC and CRFSC.

You need to describe what is involved in the course and your interest in course and students.
Exams and marks get down to individual students, but we need to hear your requirements.

Here is a generic student exam model -parts of which may be useful to you.

 

[Oblio1963]

It was much easier for me to simply create an array of all of the possible roles and course types in Excel, and simply import them as a lookup table back into Access to reference.

I apologize for not getting back to you on this sooner, however, that project had to be put on hold and with the Holidays, well, I am sure you understand I hope!

Thanks, and I hope you have a great day!