Create a relationship - Access
This Microsoft Access tutorial shows how to create relationships between tables using the Edit Relationships window. Watch more at. Link tables in Access desktop databases by adding joins and creating Now let's look at the relationship between a Products table and an Orders table. If you have questions on creating table relationships, call the Let's take a closer look at the three different ways that data in one table can be.
When you're done, close the Show Table dialog box. Show Table Dialog Box You should now see a relationship map that contains all of the tables that were selected.
Relationship Map To move a table that appears in the relationship map: Place your mouse over the table you want to move. Hold down the left mouse button, then drag the table to a new location. Release the mouse button to drop the table in its new place.
Understanding the relationship map The relationship map lists all of the tables that were selected to relate, as well as all of the fields that were previously set up for that table. Notice that the first field has a key icon next to it.
This is the primary key for the table. Primary Keys Primary and foreign keys A primary key is the first field in each table of the database. You may recall that this field auto-numbers by default, so every record in the table has its own unique number to identify it. Access uses this number to quickly pull information together when you run queries or reports, which are covered later. A foreign key is a field that is the primary field in its own table but that shows up in another table.
Create a relationship
These fields are the primary key in their own tables, but in the Orders table, they are considered foreign keys. Foreign Keys There are a few ways to establish relationships between tables: Using the Edit Relationships command located on the Design tab of the Ribbon Using the drag-and-drop method Both methods give you the same end result, but the drag-and-drop method is much easier and saves you several steps. To relate tables with the drag-and-drop method: Select a field name from one table by holding down the left mouse button.
Relationship Map Drag the field name from one table to the other table in the desired relationship. Drop the first field name onto the field name you want to relate by releasing the left mouse button.
The Edit Relationships dialog box appears. This option is explained in detail below.
Guide to table relationships - Access
Access allows for several different types of relationships. One-to-One One-to-Many Many-to-Many The relationship type you will come across most frequently—and the one created in our bookstore scenario—is the One-to-Many relationship.
One-to-Many The One-to-Many relationship means data for that field will show up a single time in one table but many times in the related table. Select one or more tables, and then click Add. After you have finished adding tables, click Close.
- In this article
- In this topic
- Create a relationship in an Access desktop database
Drag a field typically the primary key from one table to the common field the foreign key in the other table. To drag multiple fields, press the Ctrl key, click each field, and then drag them. The Edit Relationships dialog box appears.
Verify that the field names shown are the common fields for the relationship. If a field name is incorrect, click on the field name and select the appropriate field from the list.
To enforce referential integrity for this relationship, select the Enforce Referential Integrity box.
Create, edit or delete a relationship - Access
When you are finished in the Relationships window, click Save to save your relationship layout changes. Access draws a relationship line between the two tables. If you selected the Enforce Referential Integrity check box, the line appears thicker at each end. This means that the Indexed property for these fields should be set to Yes No Duplicates. If both fields have a unique index, Access creates a one-to-one relationship.Creating a one to many relationship in a Access 2013 College Database
This means that the Indexed property for this field should be set to Yes No Duplicates. The field on the many side should not have a unique index. It can have an index, but it must allow duplicates.
When one field has a unique index, and the other does not, Access creates a one-to-many relationship. Create a relationship in an Access web app The Relationships window isn't available in an Access web app.
Create, edit or delete a relationship
Instead of creating a relationship in an Access web app, you create a lookup field that gets values from a related field in another table. The field that your lookup will use as the source for values must already exist before you create your lookup field. Open the table where you want to create a new lookup field by double-clicking it in the navigation.
In the above example, click the Employees table.