How to Set Table Relationships in Access

In this article we will teach you teach you how to Set Table Relationships in Access. For this example we have taken Microsoft Acess 2016 for reference, we hope that the trick will work well for higher version of Microsoft Acess. (Set Table Relationships in Access)

About Microsoft Access: (Set Table Relationships in Access)

Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft 365 suite of applications, included in the Professional and higher editions or sold separately.

Microsoft Access stores data in its own format based on the Access Jet Database Engine. It can also import or link directly to data stored in other applications and databases.

Step by Step Guide to Set Table Relationships in Access

Step 1:
Click the Database Tools tab on the Ribbon.
* The Relationships group appears on the Ribbon.

Step 2:
From the Relationships group, click the Relationships button.
* The Relationships window appears along with the Show Table dialog box.

Step 3:
For each pair of tables you want in the relationship, click the table and then click Add.

* In the big Relationships workspace, a little window lists the fields in the selected table. As you add tables to the layout, a separate window appears for each table. You can see these windows to the left of the Show Table dialog box.

* Repeat Step 3 for each pair of tables you want to relate. If one of the tables in the pair is already present (due to an existing relationship it has with another table), you don’t have to add it again.

Step 4: (Set Table Relationships in Access)
After you finish adding tables, click the Close button.
* Decide which tables you want to relate. Since the one-to-many relationship is the most common, these instructions pertain to it.

Step 5:
Put the mouse pointer on the field you want to relate in the parent table (the “one” table in the one-to-many relationship) and hold down the left mouse button. Usually the field you want to relate in the parent table is the primary key.

Step 6:
While holding down the left mouse button, drag the mouse pointer from the parent field to the child field in the child table (the “many” table in the one-to-many relationship).
* The fields typically have the same name, such as LocationID.

Step 7:
Point to the related field in the child table and release the mouse button.
* The Edit Relationships dialog box appears, detailing the soon-to-be relationship.

Step 8: (Set Table Relationships in Access)
In the Edit Relationships dialog box, select the Enforce Referential Integrity and Cascade Delete Related Records options.
* This ensures that when you delete a record from the parent table, Access deletes all related records in the child table.

Step 9:
Click Create to set the relationship.
* The Edit Relationships dialog box closes and a join line appears connecting the two fields, indicating that the fields are related. (Set Table Relationships in Access)