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