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]

In this tutorial:

  1. Designing Client Tables
  2. Creating a New Database
  3. Creating a New Empty Database
  4. Creating Your First Simple Table by Entering Data
  5. Creating a Table Using Application Parts
  6. Creating a Table Using Data Type Parts
  7. Creating a Table in Design View
  8. Understanding Field Data Types
  9. Setting Field Properties
  10. Nulls and Zero-Length Strings
  11. Defining Simple Field Validation Rules
  12. Defining Input Masks
  13. Defining a Primary Key
  14. Defining a Table Validation Rule
  15. Understanding Other Table Properties
  16. Defining Relationships
  17. Defining Your First Relationship
  18. Creating a Relationship on Multiple Fields
  19. Adding Indexes
  20. Multiple-Field Indexes
  21. Setting Table Design Options
  22. Creating a Default Template for New Databases
  23. Printing a Table Definition
  24. Database Limitations