This month’s tutorial will briefly describe relationships (see the August 2004 Access tutorial for a more in-depth treatment), but the focus will be on the mechanics of using the features of the Relationships window. We’ll show you how to delete relationships and create them; view a specific table and its direct relationships; print the Relationships window; and save wisely, so you don’t discard a well-designed layout.

Reviewing One-To-Many Relationships

You will be editing the relationships in Northwind tables.

In the Database window, select Tables and double-click the Products table to open it. Note that each product has been assigned a specific category (Beverages, Condiments, etc.). Close the table. Next, open the Categories table. This table only has data about product categories. Each category can appear many times in the Products table but appears only once in the Categories table. Close the Categories table.

Choose Tools from the main menu and select Relationships. Locate the Categories and Products field lists. Note that a one-to-many relationship has been established between the tables through the CategoryID field, which contains data common to both tables. Categories, the table on the one side of the relationship, has a 1 next to CategoryID; in the Products table, the table on the many side, CategoryID has the infinity sign (?) beside it. Thus referential integrity has been enforced, which (very briefly) means you cannot remove a record from the Categories table if it has related records in the Products table.

Delete A Relationship

Sometimes you will find it necessary to delete a relationship, either to change the structure of your database or to perform an operation that the relationship prohibits. Right-click the line between the Products and Categories tables (you must click directly on the line or the menu won’t appear). Choose Delete and click Yes to confirm.

To recreate the relationship, click CategoryID in the Categories table. Drag-and-drop CategoryID from the Categories table directly on CategoryID in the Products table. The Edit Relationships dialog box opens. The grayed-out table names on top should be Categories and Products. Click Create.

Note that, unlike the other relationships in the window, there are no symbols (1 and infinity) at either end of the line between the two field lists. Unlike the other lists, referential integrity has not been enforced. Right-click the line again and choose Edit Relationship. Select the Enforce Referential Integrity checkbox and click OK. The symbols are now displayed.

View A Specific Table & Its Relationships

Even in a small database like Northwind, the Relationships window is sufficiently confusing in that it can be difficult to determine the relationships for a single table. In a database with 40 tables, it can be overwhelming. You can view the relationships for a single table alone.

Choose Edit and select Clear Layout. Click Yes to confirm. The Relationships window is blank. You have not deleted or changed any relationships—all you’ve done is change the layout of the window. Choose Relationships and select Show Table. In the Show Table dialog box, double-click Products to add it to the Relationships window. Click Close. Choose Relationships and select Show Direct. All of the tables that have relationships with Products are added to the window and displayed. (If you have other field lists in the window and you want to show the direct relationships for only one table, select it and choose Relationships and Show Relationships. Note that, when there are other field lists, choosing this command will not remove any of them from the window.)

At this point, the relationship lines in the window are like tangled spaghetti. You can move the field lists around (click the title bar and drag) so the relationships can be clearly seen

Show All Relationships

Suppose you once again want to see all the relationships in the window. Choose Relationships and select Show All. Now your window is really a mess! How can you get back to that clean, easy-to-read display you began with?

Close the Relationships window. Access asks you if you want to save the changes. Remember: Access isn’t asking you about any of the actual changes you may have made in any relationships. The question refers only to the layout of the window. Click No. Choose Tools from the main menu and click Relationships, and your display is back to that as seen in Figure 1.

Print The Relationships Window

You may want to print the Relationships window for, say, your database designer. Choose File and Print Relationships. Access creates a report that can be printed like any other. (Note, however, that if you save the report, it will not be updated for changes you make in the Relationships window. You have to create another report. Also, note that creating the report saves the layout.) If you wish, choose File and Print to print the report. Close the Relationships report without saving it. Close the Relationships window, close the database, and exit Access.