MS-Access / Getting Started

Creating a Relationship on Multiple Fields

There's one last relationship you need to define in the Contact Tracking database between CompanyContacts and ContactProducts. The relationship between these two tables requires multiple fields from each table. You can start by dragging the CompanyID field from the CompanyContacts table to the ContactProducts table. Access 2010 opens the Edit Relationships dialog box.

When you first see the Edit Relationships dialog box for the relationship you are defining between CompanyContacts and ContactProducts, Access 2010 shows you only the CompanyID field in the two lists. To complete the relationship definition on the combination of CompanyID and ContactID, you must click in the second line under both tables and select ContactID as the second field for both tables. Select the Enforce Referential Integrity check box, as shown, and click Create to define the compound relationship.

If you want to edit or change any relationship, double-click the line to open the Edit Relationships dialog box again. If you want to remove a relationship definition, click on the line linking two tables to select the relationship (the line appears highlighted) and press the Delete key. Access 2010 presents a warning dialog box in case you are asking it to delete a relationship in error.

Note that once you define a relationship, you can delete the table or query field lists from the Relationships window without affecting the relationships. To do this, click the table or query list header and press the Delete key. This can be particularly advantageous in large databases that have dozens of tables. You can also display only those tables that you're working with at the moment. To see the relationships defined for any particular table or query, include it in the Relationships window by using the Show Table dialog box, and then click the Direct Relationships button in the Relationships group of the Design contextual tab on the ribbon. To redisplay all relationships, click the All Relationships button in the Relationships group.

When you close the Relationships window, Access 2010 asks whether you want to save your layout changes. Click Yes to save the relationships you've defined. That's all there is to it.

You can right-click any table in the Relationships window and then choose Table Design from the shortcut menu to open that table in Design view. You can also click Relationship Report in the Tools group of the Design contextual tab on the ribbon to create a report that prints what you laid out in the window.

[Previous] [Contents] [Next]