Once you have created tables to store your data, you will need to set up relationships between those tables.  As was discussed in Getting Started with Microsoft Access, relationships link fields that contain the same data in different tables. 

This example uses a database designed to track winners of monthly contests, along with their contact information and the prizes they chose.  Access will need to know that the Janet Smith who won in October is the same Janet Smith who lives in Michigan.  We can use the fact that the same name will be listed in all tables to connect them.

Once you have created multiple tables, you will be able to create relationships.

 

Viewing Relationships

To view and edit relationships, click Tools > Relationships.

The Relationships window will open.  This window displays all relationships that currently exist between your tables, and also allows you to create new relationships.

Make sure that the tables you want to link are displayed in the Relationships window.  Right-click on the gray workspace, then either click Show All (to show all tables) or Select Table (to choose a table to show).

To select tables individually, click Select Table, then highlight the table you want to display.  Click the [Add] button.  Keep selecting tables, then clicking [Add], until you have all the tables you want.  Then click [Close].

To remove a table from the workspace, select the table by clicking on it.  Then click [Delete].

You can move tables by clicking and dragging them around the workspace.  This can help you view your relationships more easily.

Now all the tables you want to link are displayed in the workspace.

 

Creating a Relationship

• To create a relationship between two fields in different tables, click and hold on the name of the first field.  Then drag it over to the second field, releasing the mouse button when the cursor is over the name of the second field.

The Edit Relationships window will pop up.  This window lists all the relationships between these two tables.  If you want to add another relationship between these tables at this time, you can type in the field names you want to link.  Typically one relationship is sufficient.

• Once you are sure that the right fields are selected, click the checkbox labeled “Enforce Referential Integrity.”  This ensures that data in one field will always match data in the related field.

• When you are done editing the relationship, click [Create].  The new relationship will be created.  You can see the relationship as a line between the two fields in the Relationship window.

• You can select an existing relationship by clicking on the line that represents it in the workspace. 

• Then you can edit that relationship by either clicking Tools > Edit Relationship, or by right-clicking on the line and selecting Edit Relationship.

• You can delete a relationship by selecting it, then clicking [Delete].

 

Creating the Right Relationship

Deciding which field refers to which can be confusing.  In a typical one-to-many relationship, many fields or records in one table may refer to a single field in another table. 

For example, each winner appears only once in the Winners table.  However, the same person could possibly win multiple times, and thus might appear many times in the Contests table.

To create this type of relationship, you would begin with the WinnerName field in the Winners table—the table to which the second table will refer for more information.  Drag this field into the WinnerName field on the Contests table to create the relationship.  Now whenever Access sees a name in the Contests table, it will link to the Winners table, searching for a matching entry.