|Figure 1: The One to Many Relationship.|
|Figure 2: The Many to Many Relationship.|
Lets have a go at creating a Many to Many Relationship ourselves. We will begin by downloading a database containing tblOrder and tblProducts . There is also another table called tblCustomer which is joined to tblOrder in a One to Many Relationship. We will then create the tblOrderDetails Junction Table and the Joins which make up the Many to Many Relationship.
Setting up the Exercise
- Download or create the tables to be used in this exercise - ManyToMany_Exercise Tables.
- Open the database and select the DATABASE TOOLS ribbon.
- Click the RELATIONSHIPS icon in the SHOW/HIDE group. Here are the tables we are starting off with.
Figure 3: Start of Many to Many Exercise
- Select the CREATE tab on the Access Ribbon.
- Click the TABLE DESIGN icon from the TABLES group.
- Enter the first two fields in the TABLE DESIGN GRID. This is OrderId and ProductId. Select NUMBER as the DATA TYPE for both fields.
- We now need to designate both these fields as the joint primary key. Select both rows by holding down the CONTROL key on your keyboard and clicking the two blue sections on the far left of the Grid. Both rows are then highlighted. You can now click the PRIMARY KEY icon in the TOOLS group of the DESIGN ribbon. Two primary key symbols appear now on the far left of the two rows.
Figure 4: Selecting the Primary Key on two fields.
- Add the remaining two fields to the Grid. These are Quantity (DATA TYPE: NUMBER) and CostPerUnitPaid (DATA TYPE: CURRENCY).
- Close the table, saving it as tblOrderDetails when prompted.
- Select the DATABASE TOOLS tab on the Access Ribbon.
- Click the RELATIONSHIPS ICON in the SHOW/HIDE group.
- The Junction Table we just created cannot be seen at first. To make it visible, click the SHOW TABLE icon in the RELATIONSHIPS group of the Access Ribbon. Double click tblOrderDetails from the SHOW TABLE dialogue box to add it to the RELATIONSHIPS window. Close the SHOW TABLE dialogue box.
- Click and drag the new table to a location between tblOrder and tblProducts.
- We are now going to create a One to Many Relationship between the ID field of tblOrder, and the OrderId field of tblOrderDetails. Do this by clicking the tblOrder.ID field, and dragging over to tblOrders.orderId. When you release the mouse key the EDIT RELATIONSHIPS dialogue box opens. Click the three tick boxes and click the CREATE button to create the relationship.
Figure 5: The Edit Relationship Dialogue Box.
- Next we need to create a One to Many Relationship between the tblProducts.ID field and tblOrderDetails.productId. Follow the same procedure as above to create this relationship.
|Figure 6: The Many to Many Relationship.|
The screen-shot below shows an order which I created for one of the customers in the customer table.
|Figure 7: Three nested tables showing an order made by customer Tracey Smith.|
In the next screen-shot (figure 8) we see the products table. Each row is expanded to show a set of records from tblOrderDetails corresponding to them. As such, we see the orders within which each product appears:
|Figure 8: The Products Table (tblProducts) showing the order details (tblOrderDetails) for each item.|
As you can see, Order 18 (which we looked at above from the Customer Order side of the Many to Many Relationship) is seen here from the Product side, along with all other orders. If you look carefully you will see that an instance of OrderId 18 appears under each productId (ie 3,2,1, & 5) which we mentioned above. As such we see how the data contained in our tables appears from both sides of the Many to Many Relationship.
Of course, this is a somewhat fragmentary perspective from which to view our data. That is why we need forms, queries, and reports to enter, process and display all this information in a user friendly manner. This is something that I hope to cover in a future post.