Create Relationships in Microsoft Access

This Access tutorial explains how to create Relationships and the purpose of Enforce Referential Integrity.

You may also want to read:

Access Table add subdatasheet

Create Relationships in Excel PowerPivot

Access create Relationships

Create Relationships in Access is about telling Access how one table relates to another table. This process is not mandatory, you can still do everything without building any Relationships. However, if you are going to build an application, it can help you make sure all related tables are updated at the same time.

Assume that we have two tables – Compensation and Job. When an employee is hired, an Employee ID is created in Job, therefore it contains all Employee ID. Afterward a compensation record is created.

Compensation
Employee ID Salary Allowance
1 10000 100
2 20000 200
3 30000 300
4 40000 400
5 50000 500
6 60000 600

 

Job
Department Employee ID Employment Date
Account 1 1/1/2000
Admin 2 1/1/2001
Admin 3 1/1/2002
Admin 4 1/1/2003
Payroll 5 1/1/2004
HR 6 1/1/2005
Finance 7 1/1/2006

In Access 20013, click on DATABASE TOOLS tab >Relationships

access_create_relationship

You will see there are three Tables inside. These are default Relationships, you can ignore them.

access_create_relationship_02

Drag the Job and Compensation tables from Navigation Pane to Relationships.

access_create_relationship_05

Highlight Employee ID in Job, hold down mouse button and drag over to Employee ID in Compensation.

It is important that you drag from Job to Compensation but not the other way round. The table you drag from (Job) is the master table storing the Employee ID, any changes you make to Employee ID is made in this table first. The table you drag to (Compensation) is the table being affected by the change of master table.

Now the below menu pops up.

access_create_relationship_06

Relationship Type

At the bottom, the Relationship Type reflects whether the selected fields are Primary Key.

access_create_relationship_11

One to One – Both Employee ID are Primary Key (you must enable Enforce Referential Integrity to see the number 1 on the line)

access_create_relationship_08

One to Many – Only one of the Employee ID is Primary Key (you must enable Enforce Referential Integrity to see the number 1 and many symbol on the line)

access_create_relationship_09

Indeterminate (Many to Many) – Both Employee ID are not Primary Key

access_create_relationship_07

Data Integrity

Above Relationship Type, there are three options relating to data Integrity.

access_create_relationship_12

You must add at least one of the Employee ID as Primary Key in order to enable Enforce Referential Integrity and its sub options. (Not allow Many to Many relationship)

Enforce Referential Integrity (add data)

This option prevents users to add a new Employee ID in Compensation where the new Employee ID does not exist in Job.

Cascade Update Related Fields (update data)

When checked, whenever you update Employee ID in Job, Employee ID in Compensation also changes.

Cascade Delete Related Fields (delete data)

When checked, whenever you delete Employee ID in Job, records containing that Employee ID in Compensation also get deleted.

Effect of Relationships on Subdatasheet

In the Design View of Job table, the property Subdatasheet Name is [Auto] by default.

access_create_relationship_10

After adding the Relationships, you can see the linked Compensation data in Job table Datasheet View.

If you don’t like it, you can change the Subdatasheet Name property to [None].

access_create_relationship_13

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff191810.aspx

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *