Creating many-to-many relationships is different from the other two types of relationships only in that many-to-many relationships take about twice as many steps as the other two. Many-to-many relationships are in fact two one-to-many relationships. You can see that here in this figure:
Note that in this case, the Projects table is referred to as the Junction table, in that it creates a junction between the Clients and Consultants tables.
Some of your many-to-many relationships will simply evolve from you establishing one-to-many relationships. But there are those cases where you will purposefully create a many-to-many relationship, so here are the general steps:
- Create the tables necessary for your data. Remember that because Access represents many-to-many relationships by using two one-to-many relationships, you will be using three tables. Again, look at the figure above to see how the three tables are related.
- Open the Relationships Layout window, by choosing Relationships from the Tools menu.
- Clear the Relationships Layout window by clicking the Clear Layout toolbar button.
- Use the Add Table dialog to add the tables you want. In this case, Clients, Projects, and Consultants.
- Create the new relationships by using drag and drop with the necessary fields, as described in the last lesson.
View the following images which describe the steps listed above.