You use the Relationships window to establish relationships between Access tables, as shown in Figure 1.
To open the Relationships window, you must select Relationships from
the Relationships group on the Database Tools tab of the Ribbon. If you
have not established any relationships, the Show Table dialog box
appears. The Show Table dialog box allows you to add tables to the
Relationships window.
By looking at the Relationships window, you can see
the types of relationships for each table. All the one-to-many and
one-to-one relationships defined in a database are represented with
join lines. If you enforce referential integrity between the tables
involved in a one-to-many relationship, the join line between the
tables appears with the number 1 on the “one” side of the relationship
and with an infinity symbol on the “many” side of the relationship. A
one-to-one relationship appears with a 1 on each end of the join line.
Establish a Relationship Between Two Tables
To establish a relationship between two tables, you follow these steps:
1. | Open the Relationships window.
|
2. | If
this is the first time that you’ve opened the Relationships window of a
particular database, the Show Table dialog box appears. Select each
table you want to relate and click Add.
|
3. | If
you have already established relationships in the current database, the
Relationships window appears. If the tables you want to include in the
relationship do not appear, click the Show Table button in the
Relationships group of the Design tab of the Ribbon. To add the desired
tables to the Relationships window, select a table and then click Add.
Repeat this process for each table you want to add. To select multiple
tables at once, press Shift while clicking to select contiguous tables
or press Ctrl while clicking to select noncontiguous tables; then click
Add. Click Close when you are finished.
|
4. | Click and drag the field from one table to the matching field in the other table. The Edit Relationships dialog box appears. |
5. | Determine
whether you want to establish referential integrity and whether you
want to cascade update related fields or cascade delete related records
by enabling the appropriate check boxes (see Figure 2).
|
6. | Click OK. The dialog closes and you return to the Relationships window.
|
Following Guidelines for Establishing Relationships
You must remember a few important things when
establishing relationships. If you are not aware of these important
gotchas, you could find yourself in some pretty hairy situations:
It is important to understand the
correlation between the Relationships window and the actual
relationships established within a database. The Relationships window
lets you view and modify the existing relationships. When you establish
relationships, Access creates the relationship the moment you click OK.
You can delete the tables from the Relationships window (by selecting
them and pressing Delete), but the relationships still exist. The Relationships window provides a visual
blueprint of the relationships that are established. If you modify the
layout of the window by moving around tables, adding tables to the
window, or removing tables from the window, Access prompts you to save
the layout after you close the Relationships window. Access is not
asking whether you want to save the relationships you have established;
it is simply asking whether you want to save the visual layout of the
window.
When you’re adding tables to the
Relationships window by using the Show Tables dialog box, it is easy to
accidentally add a table to the window many times. This is because the
tables you are adding can hide behind the Show Tables dialog box, or
they can appear below the portion of the Relationships window that you
are viewing. If this occurs, you see multiple occurrences of the same
table when you close the Show Tables dialog box. Access gives each
occurrence of the table a different alias, and you must remove the
extra occurrences.
You can add queries
to the Relationships window by using the Show Tables dialog box.
Although this method is rarely used, it might be useful if you
regularly include the same queries within other queries and want to
permanently establish relationships between them.
If
you remove tables from the Relationships window (remember that this
does not delete the relationships) and you want to once again show all
relationships that exist in the database, you can click All
Relationships in the Relationships group on the Design tab of the
Ribbon. All existing relationships are then shown.
To delete a relationship, you can click the join line and press Delete.
Modifying an Existing Relationship
Modifying an existing relationship is easy. Access
gives you the capability to delete an existing relationship or to
simply modify the nature of the relationship.
Delete a Relationship Between Tables
To permanently remove a relationship between two tables, you follow these steps:
1. | Click the Relationships button in the Relationships group on the Database Tools tab of the Ribbon.
|
2. | Click the line joining the two tables whose relationship you want to delete. |
3. | Press Delete. Access prompts you to verify your actions. Click Yes.
|
Modify a Relationship Between Tables
You often need to modify the nature of a relationship rather than remove it. To modify a relationship, you follow these steps:
1. | Click the Relationships button in the Relationships group on the Database Tools tab of the Ribbon.
|
2. | Double-click the line joining the two tables whose relationship you want to modify.
|
3. | Make the required changes.
|
4. | Click OK. All the normal rules regarding the establishment of relationships apply. |
