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 2 one-to-many relationships
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.
View the following images which describe the steps listed above.

These are the tables to be related
1) Here are three tables to be related

After you click the ClientNo field in Clients, you drag the cursor over to the ClientNo field in Projects and let go of the mouse
2) After you click the ClientNo field in Clients, you drag the cursor over to the ClientNo field in Projects and let go of the mouse.

The Edit Relationships dialog appears. Access sees the primary key in the Clients table, the foreign key in Projects, and knows this is a one-to-many relationship
3) The Edit Relationships dialog appears. Access sees the primary key in the Clients table, the foreign key in Projects, and knows this is a one-to-many relationship.

Check the referential integrity checkbox so that one-to-many relationship will be reflected in the next screen.
4) Check the referential integrity checkbox so that one-to-many relationship will be reflected in the next screen. Then you click the Create button.

You can now see the first relationship established for the one-to-many relationship.
5) You can now see the first relationship established for the one-to-many relationship.

You can now see the first relationship established for the one-to-many relationship.
6) Now you repeat the same steps for the Projects and Consultants table. You would start by clicking the ConsultantID field Projects, the dragging the cursor over the ConsultantID field in Consultants and letting go of the mouse

The Edit Relationships dialog again appears.
7) The Edit Relationships dialog again appears.

You check the Referential Integrity checkbox and then click the Create button
8) Again, you check the Referential Integrity checkbox and then click the Create button

Apply, Filter, Sort
9) The diagram above shows the final relationship between tables

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