Creating Relationships in Microsoft Access 2007

of 06

Getting Started

Mike Chapple

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

First, you'll need to start Microsoft Access and open the database that will house your new form. In this example, we'll use a simple database I've developed to track running activity. It contains two tables: one that keeps track of the routes that I normally run and another that tracks each run.

of 06

Start the Relationships Tool

Mike Chapple

Next, you'll need to open the Access Relationships Tool. Begin by selecting the Database Tools tab on the Access ribbon. Then click the Relationships button, as shown in the image above.

If you're not familiar with the use of the Access 2007 ribbon, take our Access 2007 User Interface Tour.

of 06

Add the Related Tables

Mike Chapple

If this is the first relationship you've created in the current database, the Show Tables dialog box will appear, as shown in the image above.

One at a time, select each table that you'd like to include in the relationship and click the Add button. (Note: you can also use the Control key to select multiple tables.) Once you've added the last table, click the Close button to continue.

of 06

View the Relationship Diagram

Mike Chapple

You'll now see the blank relationship diagram, as shown in the image above.

In our example, we'll be creating a relationship between the Routes table and the Runs table. As you can see, we've added both of those tables to the diagram. Notice that there are no lines joining the tables; this indicates that you do not yet have any relationships between those tables.

of 06

Create the Relationship Between the Tables

Mike Chapple

It's showtime! In this step, we create the relationship between the two tables.

First, you'll need to identify the primary key and the foreign key in the relationship. If you need a refresher course on these concepts, read our Database Keys article.

Once you've identified them, click on the primary key and drag it to the foreign key. You'll then see the Edit Relationships dialog, as shown in the image above. In this case, we want 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 in this step, you'll need to decide whether you want to enforce referential integrity. If you select this option, Access will ensure that all records in the Runs table have a corresponding record in the Routes table at all times. As you can see, we've selected referential integrity enforcement.

Once you've finished, click the Create button to close the Edit Relationships dialog.

of 06

View the Completed Relationships Diagram

Mike Chapple

Finally, review the completed relationships diagram to ensure that it correctly depicts your desired relationship. You can see an example in the image above.

Notice that the relationship line 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 our Introduction to Relationships. You also may wish to review the following definitions from our Databases Glossary:

Congratulations! You've successfully created a relationship between two Access tables.