Create a Relationship in Microsoft Access 2013

• Modify a Form
• Create a Query

A primary feature of relational databases is that they contain multiple tables, each of which can have a relationship with any of the other tables. So far we've only created one table and it's a bit difficult trying to establish a relationship with another table when you're the only table in the database!

Our database needs more than one table anyway, because we need to be able to track not only customers, but also products, as well as the products the customers actually purchase.

So let's create two more tables and establish a relationship between them. One table will hold product information, the other will hold order information.

Create the Related Tables

Go ahead and create two tables as follows.

Be sure to set a format and default value for the DateCreated fields on both tables (the user shouldn't need to enter this field - the system can do that).

Establish the Relationship

Now that you've created two extra tables, our database has three tables. Now it's time to create the relationship between all three tables.

1. While viewing a table in Design view, and ensuring that the DESIGN tab is selected, click Relationships from the Ribbon:
   
2. A Show Table dialog box will pop up, displaying all three tables. Select all of them and click Add:
   
3. Click Close to close the dialog box
4. You will now see three boxes which represent your three tables. Click and drag the CustomerId from the Customers table across to the corresponding CustomerId field on the Orders table:
   
5. The Edit Relationships dialog will pop up. Click Enforce Referential Integrity so that it is checked. Check that the values are the same as the following screenshot and click OK:
   
   You will now see a line established between the CustomerId field on the Customers table and the CustomerId on the Orders table.
6. Now do the same for the Products table. That is, click and drag the ProductId from the Products table across to the corresponding ProductId field on the Orders table. Your table relationships should look like the ones in the following screenshot:
   

Relationship Types

We just established a many-to-many relationship. There are three types of relationships that you can establish between tables. These are as follows:

Many-To-Many Relationship

This is what our example above uses. A row in table A can have many matching rows in table B, and vice versa. In our case, a single customer can order many products, and a single product could have many customers. You create a many-to-many relationship by using a third table, called a junction table (more on that below).

One-To-Many Relationship

This is the most common relationship type. You don't need a third (junction) table for this type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. For example, a row in a Gender table (which contains the records Male and Female) can have many matching rows in a Customers table, but a row in the Customers table can only have one matching row in the Gender table. That is unless there was a business rule that allowed customers to be male and female at the same time. In this case, a many-to-many relationship would need to be established.

One-To-One Relationship

A row in table A can have only one matching row in table B, and vice versa. This is not a common relationship type, as the data in table B could just have easily been in table A. This relationship type is generally only used for security purposes, or to divide a large table, and perhaps a few other reasons.

The type of relationship that you use depends on the table structure and how the fields are defined.

Junction Table

In the above relationship, the Orders table is known as a junction table. A junction table is one that contains common fields from two or more other tables within the same database. It is used as a reference table in a many-to-many relationship (such as we are doing in our example).

Junction tables are known under many different names. Here are some: cross-reference table, bridge table, join table, map table, intersection table, linking table, many-to-many resolver, link table, pairing table, pivot table, transition table, or association table.

So if you ever hear someone mention one of those, you should have some idea what they are referring to.

Primary Key vs Foreign Key

We already know that a primary key ensures that the data in the field is unique. This is important because our Orders table needs a unique value in order to reference any record from the other tables. For example, it can't use the FirstName field because there could be more than one person with a given first name. And it can't use the DateCreated value because it's possible that two records could be created at exactly the same time (eg, if two operators are entering data or if many records are imported from an external source). This is why we need at least one field that we know will only ever contain a unique value.

A foreign key is simply the primary key's corresponding field in the related table. So in our example, the CustomerId field in the Orders table is a foreign key, while the CustomerId field in the Customers table is a primary key. Likewise, the ProductId field in the Orders table is a foreign key, while the ProductId field in the Products table is a primary key. The fields don't necessarily need to have the same names but it's a good practice to get used to. It will definitely make the database structure easier to understand - especially when you start adding more tables that also need to have relationships established.

Relationships and Queries

Creating relationships like the one above can assist in building queries against the database. Next, we're going to use our newly created relationship to help us create a query against the tables in our database.

• Modify a Form
• Create a Query