Uncle Jim's Web Designs
Development Tools for the Internet and Your Computer

Microsoft Access Tutorial


Datasheet Records Table Relationships Sorting and Filtering
Table Relationships

To prevent the duplication of information in a database by repeating fields in more than one table, table relationships can be established to link fields of tables together. Follow the steps below to set up a relational database:

  1. Click the Relationships button on the toolbar. [relationship button]
  2. From the Show Table window (click the Show Table button on the toolbar to make it appear), double click on the names of the tables you would like to include in the relationships. When you have finished adding tables, click Close.

    [Show Table window]
  3. To link fields in two different tables, click and drag a field from one table to the corresponding field on the other table and release the mouse button. The Edit Relationships window will appear. From this window, select different fields if necessary and select an option from Enforce Referential Integrity if necessary. These options give Access permission to automatically make changes to referential tables if key records in one of the tales is deleted. Check the Enforce Referential Integrity box to ensure that the relationships are valid and that the data is not accidentally deleted when data is added, edited, or deleted. Click Create to create the link.

    [Edit Relationships window]
  4. A line now connects the two fields in the Relationships window.

    [Relationships window]
  5. The datasheet of a relational table will provide expand and collapse indicators to view subdatasheets containing matching information from the other table. In the example below, the student address database and student grade database were related and the two can be shown simultaneously using the expand feature. To expand or collapse all subdatasheets at once, select Format|Subdatasheet|Expand All or Collapse All from the toolbar.
[Relational table example]

Datasheet Records Table Relationships Sorting and Filtering