Access create Relationships

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

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

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

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

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.

Relationship Type

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

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

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)

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

Data Integrity

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

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.

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].

Outbound References

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

Access create Relationships