How to Create Relationships in Microsoft Access on Windows 11

Posted on 16th June 2023

Introduction

Microsoft Access is a powerful tool for creating relational databases. In a relational database, data is organized into tables. Tables are linked together by relationships. Relationships between tables tell Access how to combine data from different tables when you want to view or query that data.

Creating a Relationship

To create a relationship between two tables, open the Relationships window by doing the following:

1. On the Database Tools tab, in the Relationships group, click Relationships.
2. If the Show Table dialog box appears, add the tables or queries that you want to include in your query, and then click Close.

After you close the Show Table dialog box, the Relationships window appears.

Creating a One-to-Many Relationship

In a one-to-many relationship, the primary key table (the table on the “one” side of the relationship) is related to the foreign key table (the table on the “many” side of the relationship).

To create a one-to-many relationship:

1. Drag the field that you want to use as the primary key from the first table to the field that you want to use as the foreign key in the second table. As you drag the field, a line appears, connecting the two fields. This line represents the relationship between the two tables.
2. When the line is complete, a dialog box appears. In the Edit Relationships dialog box, verify that the relationship is correct, and then click Create.

Creating a Self-Referential Relationship

In a self-referential relationship, a table is related to itself. This type of relationship is useful for modeling hierarchical data, such as an organizational chart.

To create a self-referential relationship:

1. Drag the field that you want to use as the primary key from the first table to the field that you want to use as the foreign key in the second table. As you drag the field, a line appears, connecting the two fields. This line represents the relationship between the two tables.
2. When the line is complete, a dialog box appears. In the Edit Relationships dialog box, verify that the relationship is correct, and then click Create.

Creating a Many-to-Many Relationship

In a many-to-many relationship, two tables are related to each other through a third table, known as a junction table. A junction table contains two fields, each of which is a foreign key to one of the other two tables.

To create a many-to-many relationship:

1. Create a new table, and add the fields that you want to use as the primary keys from the two tables that you want to relate. These fields should be of the same data type, and they should be the only fields in the table.
2. Save the table, and then close it.
3. In the Relationships window, drag the field that you want to use as the primary key from the first table to the field that you want to use as the foreign key in the second table. As you drag the field, a line appears, connecting the two fields. This line represents the relationship between the two tables.
4. When the line is complete, a dialog box appears. In the Edit Relationships dialog box, verify that the relationship is correct, and then click Create.

Viewing and Editing Relationships

The Relationships window shows all of the relationships in your Access database. You can use the Relationships window to view and edit relationships.

To view the relationships between two tables:

1. In the Relationships window, click the line that represents the relationship that you want to view.
2. The relationship appears in the upper pane of the Relationships window. The related fields appear in the lower pane.

To edit the relationships between two tables:

1. In the Relationships window, click the line that represents the relationship that you want to edit.
2. The relationship appears in the upper pane of the Relationships window. The related fields appear in the lower pane.
3. Make the changes that you want, and then click Save.

Deleting a Relationship

You can delete a relationship between two tables by doing the following:

1. In the Relationships window, click the line that represents the relationship that you want to delete.
2. On the Database Tools tab, in the Relationships group, click Delete.
3. In the Delete Relationships dialog box, click OK.

You can create a relationship between two tables in Access by using the Relationships window. To open the Relationships window, click the Relationships button on the Database Tools tab.

In the Relationships window, you can add, delete, or modify relationships between tables. To create a new relationship, click the Add button.

In the Add Relationship dialog box, you’ll need to select the primary and foreign key tables. The primary key table is the table that contains the data that you want to relate to the data in the foreign key table. The foreign key table is the table that contains the data that you want to relate to the data in the primary key table.

After you’ve selected the primary and foreign key tables, click the Create button.

You can also create a relationship by drag-and-dropping tables from the Database Explorer onto the Relationships window.

Once you’ve created a relationship, you can edit it by right-clicking on the relationship line and choosing Edit Relationship from the context menu.

You can also delete a relationship by right-clicking on the relationship line and choosing Delete from the context menu.

When you have created your tables and fields, you can start to create relationships between them. To do this, open the Relationships window by going to the Database Tools tab and clicking on Relationships. You can also press CTRL+R.

In the Relationships window, you will see all of the tables that you have created. To create a relationship, click and drag the field that you want to relate from one table to the field in the other table that you want to relate it to. For example, you might drag the field “CustomerID” from the “Customers” table to the “Orders” table.

When you release the mouse button, a dialog box will appear. In this dialog box, you can choose how the relationship will work. The most common option is “One-to-Many”, which means that for each record in the first table (in this example, each customer), there can be many records in the second table (many orders).

After you have chosen the relationship type, click Create. The relationship will appear as a line connecting the two fields.