Access 2007 for Starters: The Missing Manual

Using a Relationship

The relationship between Dolls and Manufacturers is implicit, which is a fancy way of saying that you know the relationship exists, but Access doesn't. Database pros aren't satisfied with this arrangement. Instead, they almost always define their relationships explicitly. When you create an explicit relationship, you clearly tell Access how two tables are related. Access then stores the information about that relationship in the database file.

You have good reasons to bring your relationships out into the open. Once Access knows about a relationship, it can enforce better error checking. It can also provide handy features for browsing related data and editing linked fields. You'll see all these techniques in the following sections. But first, you need to learn how to define a relationship.

Defining a Relationship

You can try out the following steps with the Bobblehead.accdb file, which is included with the online examples for this chapter. It contains the Dolls and Manufacturers tables, in their original form (with no relationships defined). The BobbleheadRelationships.accdb database file shows the final product: two tables with the right relationship.

Here's what you need to do to set up a relationship:

1. Every relationship links two fields, each in a different table. Your first step is to identify the field you need to use in the parent table.

   In a well-designed database, you use the primary-key field (Section 2.4) in the parent table. For example, in the Manufacturers ...