How to Create Relationship in Microsoft Access

Access is considered a relational database in which data is split into tables that are interconnected. Database Relationship tells you how one table is connected to the other table and how data is integrated in between them. It is important to know how relationships in Access work in order to maintain referential integrity between tables.

In this tutorial, we will teach you how to create relationship in Access.


Step # 1 – how to create a relationship

To create a relationship in Access, start by clicking on the “database tools” tab and then click on the “relationship” button. In this example we will create relationship between all our tables and also set the referential integrity between them.

steps to make a relationship

Step # 2 – Adding tables

Click on the “show tables” button; select each table and add it. Show tables button shows all the available tables created by the user in the database. From the list, you can choose the tables amongst which you want to create a relationship.

In real sense, this is the first step towards building a relationship in between tables.

including tables

Step # 3 – Creating the first relationship

Now drag the “worker id” field from the “sales people” table and drop it onto the “sales” field in the “customers” table. An edit relationship window will appear showing you the source field and destination field. Check the “enforce referential integrity” check box and then check the other two boxes as well.

making the first relationship

Step # 4 – Selecting the join type

Click on the “join type” button and select the second option. Click on create button and you will see that a relationship has been established between the “sales people” table and the “customers” table.
We select the second join type because we want to see reports on sales employees even if they didn’t have any assigned customer.
Once you click ok, you will notice that a one–to-many relationship will be established. This means that one employee can have several customers.

choosing the join type

Step # 5 – Avoiding cascade delete option

Now for the last relationship, drag the “SKU” field from the product table and drop it onto the “product” field in the order table. In the edit relationship window, checkmark the first two boxes and then click on the create button.

Once a relationship in Access is established, you will see a black line running from the source table to the destination table. You will notice a “1” and an “infinity” sign telling you the type of relationship.

In the last relationship, we selected only the “cascade update related fields” option because we don’t want the SKU to be deleted even if the products are deleted from the product line.

Not selecting the cascade delete option