Creating Relationships

Creating Relationships in Microsoft Access

On the previous page of this installment, we determined that we need to add three relationships to the Patrick’s Widgets database to implement our desired relational structure. Now it’s time to add those relationships to our database. We’ll start by creating the one-to-many relationship between the Customers table and the Orders table. Before we do that, we need to create a database diagram that we’ll use to create our relationships:

1. Select the Relationships item from the Tools menu.
2. We want to work with all four tables in our database (eventually), so highlight all four tables and click the Add button.
3. Click the Close button and you’ll be presented with a database diagram showing the database’s four tables

Once you’ve created the diagram, it’s time to start building relationships! Here’s the process for our Customers/Orders relationship:

1. Click on the CustomerID field in the Customers table and, while holding the mouse button down, drag the pointer to the CustomerID field in the Orders table and release the mouse button.
2. Verify that the two fields listed in the Edit Relationships pop-up window are correct. They should point to the CustomerID fields of the Customers and Orders tables.
3. Check the Enforce Referential Integrity and Cascade Update Related Fields boxes in the Edit Relationships window and click the Create button
4. You’ll see the link between tables appear in the diagram, with the numeral 1 next to the Customers table and the infinity symbol next to the Orders table, symbolizing a one-to-many relationship (the default for Access).

That’s all there is to it! Now repeat the process above twice to create relationships between the Orders and OrderedItems tables and the Inventory and OrderedItems tables. You’ve now created relationships and have completed building the foundation of your Access database. You should be left with a database diagram similar to that shown in the picture at the top of this page.

In our next article, we’ll learn how to add data to the database using Access forms.