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:
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.
1) Here are three tables to be related
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.
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.
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.
5) 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
7) The Edit Relationships dialog again appears.
8) Again, you check the Referential Integrity checkbox and then click the Create button
9) The diagram above shows the final relationship between tables
Creating many-to-many relationships
In database design, a many-to-many relationship is a type of cardinality that refers to the relationship between two entities A and B in which A may contain a parent record for which there are many children in B and vice versa. For instance, think of A as Authors, and B as Books. An Author can write several Books, and a Book can be written by several Authors. Because most database management systems only support one-to-many relationships, it is necessary to implement such relationships physically via a third junction table (also called cross-reference table), say,
AB with two one-to-many relationships A -> AB and
B -> AB.
In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A and B).
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
In the next lesson, you will learn how to use relationships to manage referential integrity.
