Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.
One of the goals of good database design is to delete data redundancy (duplicate data). To fulfil that goal, you split your data into many subject-based tables so that each fact is represented singularly. You then supply Access with a way to compile the divided information back together — you do this by placing common fields in tables that are related. To do this step correctly, though, you have to understand the relationships between your tables, and then specify these relationships in your database.
In this article
After you have designed a table for every subject in your database, you have to offer Access a way to allocate that information in this complete state once required. You do this by locating common fields in tables that are linked, and by defining relationships between your tables. You can then produce queries, forms, and reports that present information from several tables at once. For example, the form shown here includes information drawn from several tables:
1. Information in this form comes from the Customers table…
2. …the Orders table…
3. …the Products table…
4. …and the Order Details table.
The customer name in the Bill To box is captured from the Customers table, the Order ID and the Order Date values come from the Orders table, the Product name comes from the Products table, and the Unit Price and Quantity values come from the Order Details table. These tables are linked to each other in a variety of ways to bring information from each into the form.
In the preceding example, the fields in the tables have to be coordinated so that they display information about the same order. This coordination is accomplished by using table relationships. A table relationship works by matching data in key fields — often a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. For example, employees can be associated with orders for which they are responsible by creating a table relationship between the EmployeeID fields in the Employees and the Orders tables.
1. EmployeeID appears in both tables — as a primary key …
2. … and as a foreign key.
Types of table relationships
There are three types of table relationships in Access.
- A one-to-many relationship Let’s use an order tracking database that includes a Customers table and an Orders table as an example. A customer can place any number of orders. It follows that for any customer represented in the Customers table, there might be many orders represented in the Orders table. The relationship between the Customers table and the Orders table is a one-to-many relationship. To represent a one-to-many relationship in your database design, rearrange the primary key on the “one” side of the relationship and insert it as an extra field or fields to the table on the “many” side of the relationship. In this case, for example, you add a new field — the ID field from the Customers table — to the Orders table and name it Customer ID. Access can then use the Customer ID number in the Orders table to locate the correct customer for each order.
- A many-to-many relationship Now let’s review the relationship between a Products table and an Orders table. A single order can have multiple products. On the other hand, a single product can appear on many orders. Therefore, for each record in the Orders table, there can be many records in the Products table. In addition, for each record in the Products table, there can be many records in the Orders table. This relationship is called a many-to-many relationship. Note that to detect existing many-to-many relationships between your tables, it is important that you consider both sides of the relationship. To represent a many-to-many relationship, you must form a third table, usually known as a junction table, that deconstructs down the many-to-many relationship into two one-to-many relationships. You insert the primary key from each of the two tables into the third table. As a result, the third table records each result, or instance, of the relationship. For example, the Orders table and the Products table have a many-to-many relationship that is defined by creating two one-to-many relationships to the Order Details table. One order can have many products, and each product can appear on many orders.
- A one-to-one relationship In a one-to-one relationship, all records in the first table can own only one matching record in the second table, and every record in the second table can have only one matching record in the first table. This relationship is uncommon because, most often, the information associated in this way is stored in the same table. You might use a one-to-one relationship to divide a table with many fields, to separate some of a table for security reasons, or to hold information that applies only to a subset of the main table. After you do identify this particular relationship, both tables must share a common field.
Why create table relationships?
You can make table relationships explicitly by using the Relationships window, or by dragging a field from the Field List pane. Access employs table relationships to verify how to join tables when you need them for use in a database object. There are multiple reasons why you should design table relationships before you produce other database objects, such as forms, queries and reports.
- Table relationships inform your query designs To work with records from several tables, you usually must make a query that joins the tables. The query operates by matching the values in the primary key field of the first table with a foreign key field in the second table. For example, to return rows that list all of the orders for each customer, you construct a query that joins the Customers table with the Orders table based on the Customer ID field. In the Relationships window, you can manually specify the fields to join. But, if you already have a relationship defined between the tables, Access provides the default join, according to the existing table relationship. In addition, if you use one of the query wizards, Access uses the information it collects from the table relationships you have predefined to list you with informed choices and to fill property settings with appropriate default values in advance.
- Table relationships inform your form and report designs
- Once you create a form or report, Access uses the information it harvests from the table relationships you have preset to showcase you with informed choices and to prepopulate property settings with relevant default values.
- Table relationships are the baseline upon which you can enforce referential integrity to assist the prevention of orphan records in your database. An orphan record is a record with a reference to another record that does not exist — for example, an order record that references a customer record that does not exist. Once you create a database, you split your information into tables, each of which has a primary key. You then insert foreign keys to related tables that reference those primary keys. These foreign key-primary key pairings contribute to the formation of table relationships and multi-table queries. It’s vital that these foreign key-primary key references stay synchronised. Referential integrity, which is dependent on table relationships, helps guarantee that references stay synchronised.
Understanding referential integrity
Once you design a database, you segment your database information into many subject-based tables to minimise data redundancy. You then provide Access a way to assign the entire data collectively by arranging common fields into related tables. For example, to represent a one-to-many relationship you take the primary key from the “one” table and add it as an additional field to the “many” table. To regroup the data back together, Access uses the value in the “many” table and checks the corresponding value in the “one” table. In this way the values in the “many” table reference the corresponding values in the “one” table.
Suppose you have a one-to-many relationship between Shippers and Orders and you want to delete a Shipper. If the shipper you want to delete has orders in the Orders table, those orders will become “orphans” when you delete the Shipper record. The orders will still contain a shipper ID, but the ID will no longer be valid, because the record that it references no longer exists.
The purpose of referential integrity is to stop orphans and retain references in sync to ensure that this hypothetical situation is not repeatable.
You enforce referential integrity by allowing it for a table relationship. After enforced, Access rejects any operation that bypasses referential integrity for that table relationship. This means Access will reject both updates that alter the target of a reference, and deletions that delete the target of a reference. It’s possible you might have a perfectly valid need to change the primary key for a shipper that has orders in the Orders table. For such cases, what you really need is for Access to automatically update all the effected rows as part of a single operation. That way, Access ensures that the update is completed in full so that your database is not left in an inconsistent state, with some rows updated and some not. For this reason Access supports the Cascade Update Related Fields option. When you enforce referential integrity and choose the Cascade Update Related Fields option, and you then update a primary key, Access automatically updates all fields that reference the primary key.
It’s also possible you might have a valid need to delete a row and all related records — for example, a Shipper record and all related orders for that shipper. For this reason, Access supports the Cascade Delete Related Records option. When you enforce referential integrity and choose the Cascade Delete Related Records option, and you then delete a record on the primary key side of the relationship, Access automatically deletes all records that reference the primary key.
View table relationships
To look at your table relationships, pick Relationships on the Database Tools tab. The Relationships window launches and presents any existing relationships. If no table relationships have been defined and you are opening the Relationships window for the first time, Access requests you to insert a table or query to the window.
Open the Relationships window
- Press File, and then choose Open.
- Click and access the database.
- On the Database Tools tab, in the Relationships group, pick Relationships.
If the database contains relationships, the Relationships window displays. If the database has no relationships and you are opening the Relationships window for the first time, the Show Table dialogue box emerges. Press Close to close the dialogue box.
- On the Design tab, in the Relationships group, select All Relationships.
This lays out each of the defined relationships in your database. Note that hidden tables (tables for which the Hidden checkbox in the table’s Properties dialogue box is picked) and their relationships will be hidden unless the Show Hidden Objects checkbox is chosen in the Navigation Options dialogue box.
A table relationship is symbolised by a relationship line drawn between tables in the Relationships window. A relationship that does not enforce referential integrity is denoted as a thin line between the common fields supporting the relationship. Once you click the relationship by selecting its line, the line thickens to reflect it is selected. If you enforce referential integrity for this relationship, the line appears thicker at each end. In addition, the number 1 displays over the thick section of the line on one side of the relationship, and the infinity symbol (∞) emerges over the thick portion of the line on the other side.
After the Relationships window is active, you can pick from the listed commands on the ribbon:
On the Design tab, in the Tools group:
- Edit Relationships – Launches the Edit Relationships dialogue box. After you click a relationship line, you can select Edit Relationships to modify the table relationship. You can also double-click the relationship line.
- Clear Layout – Deletes every table and relationship from view in the Relationships window. Note that this command only conceals the tables and relationships — it still retains them.
- Relationships Report – Produces a report that presents the tables and relationships in your database. The report displays merely the tables and relationships that are visible in the Relationships window.
On the Design tab, in the Relationships group:
- Show Table – Accesses the Show Table dialogue box so that you can choose tables and queries for viewing in the Relationships window.
- Hide Table – Covers the chosen table in the Relationships window.
- Direct Relationships – Shows all relationships and related tables for the selected table in the Relationships window, if they are not already displayed.
- All Relationships Presents all of the relationships and related tables in your database in the Relationships window. Note that hidden tables (tables for which the Hidden checkbox in the table’s Properties dialogue box is picked) and their relationships will remain hidden unless Show Hidden Objects is confirmed in the Navigation Options dialogue box.
- Close – Closes the Relationships window. If you made any alterations to the layout of the Relationships window, you are asked if you want to save those changes.