Create a one to many relationship access

Creating relationships in Access 2016 is basically the key to creating databases. But why would you want to create relationships in Access? What are the benefits of creating relationships in Access? And, of course, where do you start creating a relationship in Access. Let's take a look at an example. (BTW if you want to follow along then you can use this Access Exercise File).

Also this is the second in a series of Access tutorials. The first of which is: Creating an Access Database from Planning to Creating the First Table.

OK, by now you have created your first table. You now want to connect these tables together.This tutorial will show you how to create subsequent tables that you would use in a typical customer order database. These tables will then be joined together via relationships. This training guide will show you how to create one to many relationships in Microsoft Access. We are using Microsoft Access 2016 in this training video however the concepts that you learn here can be migrated to pretty much any database system.

This free tutorial is part of a series that will show you how to create an Access 2016 database right from the planning stage all the way to forms and queries. So make sure you keep coming back to see the various updates that we have made.

Access - One to Many Relationship

You can access this view by going to the relationships window in Access. (Database Tools - Relationships).

Have a look at the above two tables. Customer details are recorded in tblCustomers and tblOrders will contain the orders for each customer. We would like to relate the orders for each customer so that we can know which orders relate to which customers.

Also notice that each table has a primary key. The primary key serves is a unique identifier for each record (row) of the table. It is important that you use a primary key field in each table to aid with the consistancy of the database. You will also be using the primary key to relate your tables. So, in the above example you will need a primary key in tblCustomers so that you can relate that to the tblOrder table.

The One Side of the Relationship

First, let's talk about the one side of the relationship. Note that the primary key field in tblCustomers is called customerID. We DO NOT want to repeat the customerID field, each customerID should be unique. We will be using the customerID field in the one side of the relationship. This way we can ensure that each customer is unique. This will form the many side of the relationship.

The Many Side of the Relationship

To be able to relate the two tables well will need to add the customerID field to the tblOrders table. If you look at the picture above you can see that we have yet to add the customerID field. Let's do that.

So, now we have added the customerID field to the tblOrders we go back to the relationships window.

Now, drag between customerID in tblCustomers and customerID in tblOrders. When you do this the Edit Relationships box appears.

Referential Integrity

You will notice a check box in the Edit Relationships box that's entitle Enforce Referential Integrity. What does that mean?

Say, you want to enter an order for a customer. You want to make sure that each order matches up to an existing customer. You wouldn't want an order entered that wasn't related to a customer. Think of the problems that would cause one of which would be that you wouldn't know who placed the order. That's what referential integrity will ensure.

In fact, with referential integrity you cannot enter a record in tblOrders without there being an existing record in tblCustomers. Basically, before you add a value in customerID in the tblOrders table, the same customerID must also exist in tblCustomers.

So, should you tick the Referential Integrity box? I would say yes. It does mean that you get a few more referential integrity errors when creating your database. But, in the long run, it will make your databases a lot more stable and prevent what is known as orphaned records. An orphaned record is a record that has an entry in the many, also called the child, side of the relationship. But does not exist in the one, also called the parent, side of the relationship.

You can see Access representing the Referential Integrity between the tables by a small 1 beside the one side of the relationship next to the primary key column. The infinity sign is displayed next to the many side of the relationship. The related field in the many side of the relationship is also known as the foreign key.

Viewing the Relationship

Now that you have created your first relationship in Access 2016 what do you get out of it? What are the benefits?

If you open your tblCustomers table in datasheet view, you will see a little plus to the left of each row.

When you click on the plus symbol, you can now add as many orders as you like against a single customer. You don't have to repeat information that pertains solely to the customer. Such as their name, address, phone numbers etc.

You might be interested that this database structure goes a long way to creating what is known as the third normal form. If you want more detail about databases and relationships and the rules of normalisation you can check out the following sites:

How do I create a one