1. Choose Tools → Relationships.
  2. From the Show Table dialog box, select each table for which you want to define relationships, and click Add.
    • If you do not see the Show Table dialog box, click the Show Table button on the toolbar or right-click and choose Show Table.

      Access Show Table button

    • Once tables are added, you may not be able to see all fields.  While you can scroll to view fields elsewhere in the table’s field list, it will be easier for you to work if you simply resize the field list—drag its border.

  3. Drag a related field from one table and drop it on top of its related field in another table.
  4. Click the Enforce Referential Integrity checkbox.  Referential Integrity helps prevent data entry mistakes.  If a user entering data into the ‘many’ table enters an option that does not exist in the ‘one’ table, an error message is displayed and Access will not permit the entry.
  5. Select any additional Referential Integrity options you desire to apply. 
    • Cascade Update Related Fields:  If a user changes data in the related field (key) of the ‘one’ table, all records in the ‘many’ table are updated to reflect that change.  In other words, the many table is updated to maintain links between records, even though the related field has changed.
    • Cascade Deleted Records:  If a user deletes a record from the ‘one’ table, any records in the ‘many’ table that matched the deleted record will also be deleted.
  6. Click Create.