Access 2007: Table Relationships
In a relational database, data is divided among two or more tables that share at least one common field (for example, a student, a course number, and so on). After you have created the tables, the next step is identifying the linked fields in each to build a relationship. There are some rules to consider when creating table relationships. The linked field names do not need to be identical, but the data type for each must be the same (for example, you cannot link a text field to a numeric field).
Normally the field linked is one table's primary key (a unique identifier for each record), but usually is not the other table's primary key (although it can occur). The linked field in the second table is referred to as the foreign key (a value corresponding to the primary key within the first table). Avoid linking fields containing information that may be duplicated or changed (for example, two instructors with the same name or a course name that is changed).
Following are the three different types of table relationships:
- One-to-many: Relationships that connect one record in the first table to many records in another table. This is the default and the most common type which consists of a parent and a child table.
- One-to-one: Relationships that line one record in the first table to just one record in another table. This type is uncommon since such information generally would be in a single table.
- Many-to-many: Relationships that link a number of records in one table to multiple records in another table. This type in a situation like a class that needs to be available to every potential student.
Older versions of Access required
a third table (called a junction table) to accomplish a many-to-many relationship.
Access 2007 now allows the creation of many-to-many relationships between
two tables via multivalue fields. This is an advanced topic and will not
be covered in this tutorial. For more information, click 
[Online
Help button] and search for "Table Relationships."
To add a table relationship:
- Use either Datasheet or Design view. Save and close the tables you plan on linking.
- Select the Database Tools tab and click
[Relationships
button] in the Show/Hide group; a Relationships tab
appears. - Click
[Show
table button] in the Relationships group on the Design contextual
tab. - Add the tables to relate from the Show Table dialog
box by selecting
a table name and clicking
;
close
the dialog box when finished. - Create a one-to-one relationship by selecting the field to relate in the parent table (usually the primary key), holding down the mouse button, and dragging to a related field in the child table.
- In the Edit Relationships dialog box that appears, select the option Enforce Referential Integrity (to prevent changes that violate the rules of a relationship).
- Verify the two field names are correct and then click
.
The line that appears between the two common fields indicates the tables
are now related.
If any tables appear on the Relationships tab, it means relationships have previously been defined for the database. If more than one person has access to the database, check with the others before changing a relationship.
To edit or remove a table relationship:
- Use either Datasheet or Design view.
- Open the Relationships tab as described above.
- Right-click the line joining the two tables.
- Make changes by selecting "Edit Relationship" from the pop-up menu that appears, or remove a relationship by selecting "Delete".