Creating Database Relationships in Access

One of the major advantages of databases like Microsoft Access is their ability to maintain relationships between different data tables. The power of a database makes it possible to correlate data in many ways and ensure the consistency (or referential integrity) of this data from table to table.

Let's take a look at the process of creating a simple relationship using a Microsoft Access database.

How to Make an Access Relationship

1. With Access open, go into the Database Tools menu at the top of the program. From within the Relationships area, select Relationships.

   

2. The Show Table window should appear. If it doesn't, choose Show Table from the Design tab. From the Show Table screen, choose the tables that should be involved in the relationship, and then select Add.

   If the database already features mapped relationships—usually because of existing forms, reports, or queries—then Access bypasses this pop-up and instead move straight to the Design view of the Relationships window.

   

3. Drag a field from one table to the other table so that the Design window opens. If your database already infers relationships, this window will already populate with relationships.

   Hold down the Ctrl key to select multiple fields; drag one of them to drag all of them over to the other table.

   

4. Choose any other options you want, like Enforce Referential Integrity or Cascade Update Related Fields, and then select Create or Create New.

   Selecting enforce referential integrity means that the database will not accept data that doesn't match the relationship. The two cascade options forces the database to purge or update when the source record changes. For example, selecting cascade update related fields will prompt the database to correct the value in the related table when a value in the source table changes; if it's left unchecked, the old values remain, and new records earn the new value.

   

Join Types

Access supports three types of joins through this wizard—one-to-one, one-to-many, and many-to-one. In general, you'll typically use the first join type, which links the data when the records in one match the records in the other.

Access supports other kinds of joins, but you'll have to manage those through advanced tools, not through the Relationships window.