Access tutorial · many-to-many
Many to Many Relationship
In their simplest form Many to Many relationships are just two columns in a table. The first column representing one side of the Many to Many Relationship and the other column representing the other side of the Many to Many Relationship. In this particular example we have students against hobbies. The information represents a student in the left-hand column and the right hand column represents a hobby that this student may be taking. You can also read the table backwards as it were, you could ask the question, which students are taking this particular hobby?
Videos that used to sit on this page may return later.
The Many to Many name can be misleading because in essence, you only have one student related to many hobbies, and the reverse, one hobby related to many students. But as you can see in the table, you have many students on the left and many hobbies on the right, hence the misleading name – "Many to Many"!
The sections below demonstrate the use of a subform in a Many to Many Relationship. They show the many to many relationship from both sides: students matched against hobbies, and, reading the junction table backwards, hobbies matched against students. There is also a section showing you one or two problems you might have, and the solution(s).
The tables
There are three tables: tblStudent, tblHobby and the junction table tblStudentHobby, which holds the two columns — one student against one hobby per row.
| Table | Field | Data Type |
|---|---|---|
| tblStudent | StudentID | AutoNumber (Primary Key) |
| tblStudent | StudentName | Short Text (255) |
| tblHobby | HobbyID | AutoNumber (Primary Key) |
| tblHobby | HobbyName | Short Text (255) |
| tblStudentHobby | ID | AutoNumber (Primary Key) |
| tblStudentHobby | StudentID | Number (Long Integer) |
| tblStudentHobby | HobbyID | Number (Long Integer) |
Here's what tblStudentHobby holds in the sample — the student on the left, the hobby on the right:
| ID | StudentID | HobbyID |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 2 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
| 6 | 3 | 3 |
| 7 | 2 | 3 |
| 8 | 1 | 9 |
| 9 | 1 | 8 |
| 10 | 1 | 7 |
Read it one way and student 3 has hobbies 1, 2 and 3. Read it backwards and hobby 1 is taken by students 1, 2 and 3.
Adding Hobbies to a Student Database
Master & Sub-Form
Build a student form (frmStudent), then a datasheet form based on the junction table, and drag the datasheet form onto the student form as a subform. As you flick through the students, the StudentID in the subform stays synchronised with the student on the main form, so any hobbies you add are added against that student. Finally, change the StudentID/HobbyID box in the subform into a combo box based on the Hobby table, so you see the hobby name rather than a number.
The full step-by-step is here: Adding Hobbies Transcript.
Adding Student to a Hobbies Database
Master & Sub-Form
In the sample database the main form is frmStudent, and it shows one student at a time. Sitting on it is the subform sfrmStudentHobby, a datasheet that's linked to the main form on StudentID, so it only ever shows the hobbies for the student you're looking at.
To give a student a hobby, you pick it from the combo box cboHobbyID in the subform. The combo box is bound to HobbyID, but it shows you the HobbyName from tblHobby, because I've hidden the first column by setting the Column Widths to 0;2. Limit To List is set to Yes, so you can only choose a hobby that's already in the list.
Every hobby you pick saves one row in the junction table tblStudentHobby: just the StudentID and the HobbyID. That's the whole trick. One student can have as many hobbies as they like, one hobby can have as many students as it likes, and you never need Hobby1, Hobby2, Hobby3 columns.
Adding a new student is just as easy. Go to a new record on frmStudent and type in the student's name, then add their hobbies in the subform, one row for each hobby.
Synchronisation not Working
Master Child Link
Here's a main form / subform arrangement that hasn't been synchronised properly. You can clearly see that the data is not synchronised correctly. The fix is to set up the Master Child link with the Subform Linker Dialogue Box, so the record in the subform follows the record in the main form.
In the sample database the subform is linked to the main form on StudentID.
Sample database
Download the sample (Access 2007+, sample data only): StudentHobby.zip
Posts related to this page
The link below relates to a discussion about this subject. Visit it and you may find someone else has already solved the problem you are having. This concept has many names:- "Associative entity" "Junction table"