Database Relationships  «Prev  Next»

Lesson 4Creating many-to-many relationships
Objective Create a many-to-many relationship.

Creating Many-to-Many Relationships in Access

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:

Many-to-many relationships are in fact two "one-to-many" relationships

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:
  1. 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.
  2. Open the Relationships Layout window, by choosing Relationships from the Tools menu.
  3. Clear the Relationships Layout window by clicking the Clear Layout toolbar button.
  4. Use the Add Table dialog to add the tables you want. In this case, Clients, Projects, and Consultants.
  5. Create the new relationships by using drag and drop with the necessary fields, as described in the last lesson.
Just to bring this home I would like to have you walk through the following Slide Show.

Creating many To Many Relationships
In the next lesson, you will learn how to use relationships to manage referential integrity.

Database Relationships - Quiz

Click the Quiz link below to answer a few questions about working with different types of relationships. Database Relationships - Quiz

Creating Many To Many Relationships - Exercise

Taking what you just saw in the SlideShow, work on the following exercise.
Creating Many To Many Relationships - Exercise