Knowledge Is Power

About relationships in an Access database

This topic provides reference information about:

Why you should define relationships

How relationships work

A one-to-many relationship

A many-to-many relationship

A one-to-one relationship

Defining relationships

Why define relationships?

After you've set up different tables for each subject in your Microsoft Access database, you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships between your tables. After you've done that, you can create queries, forms, and reports to display information from several tables at once. For example, this form includes information from five tables:

How do relationships work?

In the previous example, the fields in five tables must be coordinated so that they show information about the same order. This coordination is accomplished with relationships between tables. A relationship works by matching data in key fields — usually a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. For example, employees can be associated with orders they're responsible for by creating a relationship between the Employees table and the Orders table using the EmployeeID fields.

A one-to-many relationship

A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.

A many-to-many relationship

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields — the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table. For example, the Orders table and the Products table have a many-to-many relationship that's defined by creating two one-to-many relationships to the Order Details table.

A one-to-one relationship

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. For example, you might want to create a table to track employees participating in a fundraising soccer game.

Defining relationships

You define a relationship by adding the tables that you want to relate to the Relationships window, and then dragging the key field from one table and dropping it on the key field in the other table. You can also define relationships by using the keyboard.

The kind of relationship that Microsoft Access creates depends on how the related fields are defined:

• A one-to-many relationship is created if only one of the related fields is a primary key or has a unique index.
• A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.
• A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields — the foreign keys from the two other tables.

Note: If you drag a field that isn't a primary key and doesn't have a unique index to another field that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join line between the tables, but referential integrity won't be enforced, and there's no guarantee that records are unique in either table.

Define relationships by using the keyboard

1. Close any tables that you have open. You can’t create or modify relationships between open tables.
2. If you haven’t already done so, switch to the Database window. You can press F11 to switch to the Database window from any other window.
3. Press ALT+T to select the Tools menu, and then press R to open the Relationships window.
4. If your database doesn’t have any relationships defined, the Show Table dialog box appears automatically. If you need to add the tables that you want to relate, and the Show Table dialog box does not appear, press ALT+R to select the Relationships menu, and then press T to open the Show Table dialog box. If the tables that you want to relate already appear in the Relationships window, skip to step 7.
5. In the Show Table dialog box, select the first table that you want to relate, and then press ENTER to add it to the Relationships window.
6. Repeat step 5 for any other tables you want to relate, and then press ALT+C to close the Show Table dialog box.
7. Press ALT+R to select the Relationships menu, and then press R to open the Edit Relationships dialog box.
8. Press ALT+N to open the Create New dialog box.
9. In the Left Table Name box, select the name of the table that contains the primary key.
10. In the Right Table Name box, select the name of the table that contains the foreign key.
11. In the Left Column Name box, select the primary key field, and in the Right Column Name box, select the foreign key field.

The related fields don’t have to have the same names, but they must have the same data type and contain the same kind of information. In addition, when the matching fields are Number fields, they must have the same FieldSize property setting. There are two exceptions to matching data types: you can match an AutoNumber field with a Number field whose FieldSize property is set to Long Integer; and you can match an AutoNumber field with a Number field if the FieldSize property for both fields is set to Replication ID.

1. Press ENTER.
2. In the Edit Relationships dialog box, use the arrow keys to make sure that the two columns contain the field names you want; you can change them if necessary, or you can specify additional fields if you are using a multiple-field primary key.

Set relationship options if necessary. For information about a specific item in the Relationships dialog box, select the item and then press F1.

1. Press ALT+C to create the relationship.
2. Repeat steps 5 through 14 for each pair of tables you want to relate.

• If you need to view all the relationships that are defined in the database, press ALT+R to select the Relationships menu, and then press L. To view only the relationships defined for a particular table, select that table, press ALT+R to select the Relationships menu, and then press D.
• If you need to make a change to the design of a table, select that table; press SHIFT+F10 to select the shortcut menu, and then select Table Design.
• You can create relationships for queries as well as tables. However, referential integrity isn't enforced with queries.
• To create a relationship between a table and itself, add that table twice. This is useful in situations where you need to perform a lookup within the same table. For example, in the Employees table in the Northwind sample database, a relationship has been defined between the EmployeeID and ReportsTo fields, so that the ReportsTo field can display employee data from a matching EmployeeID.

More Coming Soon!

|  Return to Home  | Return to Teacher Selection Page |