Creating Relationships Between Tables in Microsoft Access 2010

Serious senior male carpenter using laptop in workshop
It's easy to create a relationship between tables in Microsoft Access. Caiaimage/Agnieszka Olek / Getty Images

The true power of relational databases lies in their ability to track relationships (hence the name) among data elements. However, many database users don’t understand how to take advantage of this functionality and simply use Microsoft Access 2010 as an advanced spreadsheet. This tutorial walks through the process of creating a relationship between two tables in an Access database.

of 06

Getting Started

Opening file in Microsoft Access

 Mike Chapple

This example uses a simple database to track running activity. It contains two tables: one that keeps track of the routes that are normally run and another that tracks each run.

of 06

Start the Relationships Tool

Database tools in Microsoft Access

 Mike Chapple

Open the Access Relationships Tool by selecting the Database Tools tab on the Access ribbon. Then click the Relationships button.

of 06

Add the Related Tables

Show table dialog in Microsoft Access
Mike Chapple

If this is the first relationship you've created in the current database, the Show Tables dialog box appears.
One at a time, select each table that you want to include in the relationship and click the Add button. (Use the Control key to select several tables simultaneously.) After you've added the last table, click the Close button to continue.

of 06

View the Relationship Diagram

View relationship dialog in Microsoft Access
Mike Chapple

At this point, you'll see the blank relationship diagram. In this example, we are creating a relationship between the Routes table and the Runs table. As you can see,  both of those tables have been added to the diagram. Notice that there are no lines joining the tables, indicating that there are not yet any relationships between the tables.

of 06

Create the Relationship Between the Tables

Edit relationships in Microsoft Access

 Mike Chapple

To create the relationship between the two tables, you first need to identify the primary key and the foreign key in the relationship. If you need a refresher course on these concepts, read Database Keys.

Click the primary key and drag it to the foreign key, which opens the Edit Relationships dialog. In this example, the goal is to ensure that each run in our database takes place along an established route. Therefore, the Routes table's primary key (ID) is the primary key of the relationship and the Route attribute in the Runs table is the foreign key. Look at the Edit Relationships dialog and verify that the correct attributes appear.
Also at this point, you need to decide whether to enforce referential integrity. If you select this option, Access ensures that all records in the Runs table have a corresponding record in the Routes table at all times. In this example, referential integrity enforcement is enforced.
Click the Create button to close the Edit Relationships dialog.

of 06

View the Completed Relationships Diagram

Database diagram in Microsoft Access
Mike Chapple

Review the completed relationships diagram to ensure that it correctly depicts the desired relationship. Notice that the relationship line in the example joins the two tables and its position indicates the attributes involved in the foreign key relationship.
You'll also notice that the Routes table has a 1 at the join point while the Runs table has an infinity symbol. This indicates that there is a one-to-many relationship between Routes and Runs. For information on this and other types of relationships, read Introduction to Relationships