Managing Many-To-Many Relationships in Microsoft Access:
As mentioned earlier (see Identifying Table Relationships), RDBMSs do not support many-to-many relationships between tables. This is the example we had of this type of relationship:
| EmployeeID | Last Name | First Name | ProjectNum |
|---|---|---|---|
| EN1-26 | O'Brien | Sean | 30-452-T3 |
| EN1-26 | O'Brien | Sean | 30-457-T3 |
| EN1-26 | O'Brien | Sean | 31-124-T3 |
| EN1-33 | Guya | Amy | 30-452-T3 |
| EN1-33 | Guya | Amy | 30-482-TC |
| EN1-33 | Guya | Amy | 31-124-T3 |
| EN1-35 | Baranco | Steven | 30-452-T3 |
| EN1-35 | Baranco | Steven | 31-238-TC |
| EN1-36 | Roslyn | Elizabeth | 35-152-TC |
| EN1-38 | Schaaf | Carol | 36-272-TC |
| EN1-40 | Wing | Alexandra | 31-238-TC |
| EN1-40 | Wing | Alexandra | 31-241-TC |
| ProjectNum | ProjectTitle | EmployeeID |
|---|---|---|
| 30-452-T3 | Woodworking Around The House | EN1-26 |
| 30-452-T3 | Woodworking Around The House | EN1-33 |
| 30-452-T3 | Woodworking Around The House | EN1-35 |
| 30-457-T3 | Basic Home Electronics | EN1-26 |
| 30-482-TC | The Complete American Auto Repair Guide | EN1-33 |
| 31-124-T3 | The Sport Of Hang Gliding | EN1-26 |
| 31-124-T3 | The Sport Of Hang Gliding | EN1-33 |
| 31-238-TC | The Complete Baseball Reference | EN1-35 |
| 31-238-TC | The Complete Baseball Reference | EN1-35 |
| 31-241-TC | Improving Your Tennis Game | EN1-40 |
| 35-152-TC | Managing Your Personal Finances | EN1-36 |
| 36-272-TC | Using Electronic Mail Effectively | EN1-38 |
Above, tables with a many-to-many relationship
The usual solution is to break this relationship down into two one-to-many relationships by creating an intersection or junction table. This table would hold the primary key field from each of the tables in the many-to-many relationship. In the new table, those fields together would be a multi-field primary key resulting in the following relationships and diagram.
An intersection table and two one-to-many relationships
This forms two one-to-many relationships; each employee can work on many projects and many employees can work on a single project. Just as you might have to make a connection in Chicago to fly from Boston to Seattle, you sometimes need an intermediate or junction table to get where you need to be.
An example of Handling Many-To-Many Relationships in Microsoft Access