Many-to-Many-to-Many

[pademo57]

Hi There,


Yes, I am a newbie but our company is setting up a training program across the country at each of our 7 offices. As a trainee in the warehouse you progress through different courses, and each course has 4 levels. In other words, A Student can have many Courses, Courses can have many Students. Each Course can have 4 Phases, each Phase has to have a Daily Report, each Daily Report has to have 4 "CheckLists" completed.

So if I understand what people are saying about "Many-to-Many" relationships I would set up my database:
Employees - EmpID (Primary autonumber Key), Fname, Lname, Status, etc.
Courses - CourseID, CourseName, CourseStartDate, CourseEndDate, CourseLocation, etc.
Phases - PhaseID, PhaseName, PhaseLength, etc.
Daily Report - DailyRptID, DailyRptDate, DailyRptShift, etc.
CheckList - CheckListID, CheckListItem, CheckListPassFail, etc.

1) Because Employees can have many Courses and Courses can have many Employees I would need to have a "join" table such as, Empl_Course and connect it where Employees.EmpID and Course.CourseID would become the primary key in Empl_Course. Is that right?
2) If so, do I make another field in Empl_Course that would be an autonumber field and then connect it to the Phases table by making another field as in:
Empl_Course.EmplCourID > Phases.fkEmpCourID (foreign key from Empl_Course).

[orange]

Watch the free video here
https://www.youtube.com/watch?v=7XstSSyG8fw

[pademo57]

Watch the free video here
https://www.youtube.com/watch?v=7XstSSyG8fw

I guess I didn't explain myself very well. Although I do thank you for the video clip I already understand that part. It's what happens afterwards that I'm interested in. What I mean is the join table (which in the video has two fields combined into being the Primary Key) but I need another level beyond that. Before when I used an older version of MS Access the Join Table would have not only a foreign key from the Students and the Courses table but it would also include a field that would be the Primary Key. So you could use that Primary key in another Table, as an example:

In the Students Table - Students.studentID (Primary Key) would connect to Stud_Courses.fkStudentID (foreign key)
In the Courses Table - Courses.courseID (Primary Key) would connect to Stud_Courses.fkCoursesID (foreign key)
In the Stud_Courses (Join) Table would have StudCourID (Primary Key)
In the Daily_Report Table StudCourID would become fkStud_Cour (foreign key)

But I can't find an example of using a join table which then connects to another table (in MS Access 2010). Has it changed or is it the same thing?

[orange]

Let's consider your tables Employees (EmpID.... and Courses(CourseId...., and a Junction table Empl_Course

You can have
Empl_Course with a composite/compound Primary Key

see http://en.wikipedia.org/wiki/Compound_key for more info

EmpIdFK + CourseIDFK where the PKs of each of the Tables causing the M:M are individual fields in Empl_Course, and the combination is the PK of Empl_Course

Alternatively, you could structure Empl_Course

EmplCourseID as autonumber PK
EMPIdFK as FK to Employees
CourseIDFK as Fk to Courses

then use EmplCourseID to link to other tables as required.

In this alternative set up, you would create a unique composite index on
EmpIdPK + CourseIdFK to prevent duplicates on that combination of fields.

This alternative set up is "easier" ( less awkward/less confusing) to work with if you have other relationships between
Empl_Course and other tables( ie Empl_Course has "child tables").

For more info and similar situation see
http://www.access-programmers.co.uk/...ad.php?t=86569