Many-to-Many: Relationships, Joint Types, Queries, and Forms

[alpinegroove]

I am building an Instructor Database for a school and have some questions related to many-to-many relationships and how to implements them in Access.

Each course at the school can have any combination of Instructor, TA, and/or Reader, so I need to use the roles model.



I understand the basic idea and know that I need a juncture/link table to address the many-to-many relationships.

So the basic structure would look something like this:

tblPeople
1, Johnson, Mike
2, Davids, Mary

tblRoles
1, Instructor
2, TA
3, Reader

tblCourses
1, Baking 101
2, Carpentry 200
3, Marketing Basics 1

tblCoursePeopleRoles (Field Order: Course, Person, Role)
1, 2, 1 (Baking 101; Davids, Mary; Instructor)
1, 2, 3 (Baking 101; Davids, Mary; Reader)
3, 1, 1 (Marketing Basics 1; Johnson, Mike; Instructor)
3, 2, 2 (Marketing Basics 1; Davids, Mary; TA)

So I think I understand the theory, but I am having trouble figuring out how to make this happen in Access.

These are some of the questions that have come up:

1. How to set up the relationships between the tables, both in terms of the Joint Type and enforcing referential integrity.

2. How to create queries that display the information I need given that the tables mostly contain numbers.

3. How to create forms and subforms with combo boxes for data entry given the model above.

I think these are pretty basic questions for someone with enough Access experience. Any help would be appreciated.
Thank you!

[jzwp11]

I would join people to their roles first and then to the courses

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

tblRoles
-pkRoleID primary key, autonumber
-txtRole

tblCourses
-pkCourseID primary key, autonumber
-lngCourseNo
-txtCourseName


tblPeopleRoles
-pkPeopleRoleID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkRoleID foreign key to tblRoles

tblCoursePeopleRoles
-pkCoursePeopleRoleID primary key, autonumber
-fkCourseID foreign key to tblCourses
-fkPeopleRolesID foreign key to tblPeopleRoles

You should be able to enforce referential integrity for all joins as shown by the primary key--foreign key relationships

2. How to create queries that display the information I need given that the tables mostly contain numbers.

You would include the pertinent tables that have the descriptive text in the query also.

3. How to create forms and subforms with combo boxes for data entry given the model above.

I would wait until your table structure is finalized.

[alpinegroove]

Thank you for your reply, jzwp11.
Why is it better to first join people and roles and only then with courses?

I don't understand how the referential integrity feature would work in the case. There are also Joint Types (Include All / Only Include). How do these work?

Thanks again.

[jzwp11]

There are also Joint Types (Include All / Only Include). How do these work?

Typically you would want to join type 1 which creates an inner join. In a one-to-many relationship this means that for every record in the table that forms the many side of the relationship, you will have a matching record in the table that forms the one side of the relationship. Forcing referential integrity for this type of relationship will prevent adding records on the many side that do not have a matching record in the one side. In other words, it helps to prevent orphan records (child records with no parent). The other types of join are left/right joins. These can come in handy at times, but I generally only use them in queries. It also would depend on your data.

Why is it better to first join people and roles and only then with courses?

I took a guess based on the data you showed. You had Mary in several roles for different courses, so the role was more tied to the person and it was the person+the role that was pertinent to the course. I assumed that a person can play the same role for many courses, therefore, why repeat the same combination of person/role by using the two fields as you originally listed? Just define the person/role combination ahead of time and join that to the course.

[alpinegroove]

Thanks for the explanation.

[jzwp11]

You're welcome. Good luck with your project.