How To Add A Table To Relationships In Access

Create a Relationship in Microsoft Access 2013

A primary characteristic of relational databases is that they contain multiple tables, each of which can have a relationship with any of the other tables. And so far we've only created one tabular array and it's a bit difficult trying to establish a relationship with another table when yous'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, besides every bit the products the customers actually buy.

And then allow's create two more tables and institute a relationship between them. I table will concord product information, the other will hold order information.

Create the Related Tables

Get ahead and create two tables as follows.

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

Master Key

Recall that the central icon indicates that the field is a master key. A primary key ensures that the data in this field is unique - no two values can be the aforementioned. A table must take a primary key before a relationship can be established with another table. Access automatically sets the ID field every bit a principal key, and then as long as you simply rename ID to ProductId (or OrderId as the case may be) it will already exist a primary key. If for some reason your table doesn't have a master fundamental set, or if yous want to modify the principal key field, you lot can simply right click on the field header (in Design view), and select Main Key from the contextual menu.

Plant the Relationship

At present that yous've created two actress tables, our database has three tables. Now it'southward fourth dimension to create the relationship between all iii tables.

While viewing a tabular array in Design view, and ensuring that the Pattern tab is selected, click Relationships from the Ribbon:
A Show Table dialog box volition popular up, displaying all three tables. Select all of them and click Add together :
Click Close to shut the dialog box
You will now see 3 boxes which correspond your three tables. Click and drag the CustomerId from the Customers tabular array across to the corresponding CustomerId field on the Orders table:
The Edit Relationships dialog volition pop upwards. Click Enforce Referential Integrity so that information technology is checked. Check that the values are the same every bit the following screenshot and click OK :

Y'all will now meet a line established between the CustomerId field on the Customers table and the CustomerId on the Orders table.
At present practice 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

Nosotros simply established a many-to-many human relationship. At that place are three types of relationships that you can establish betwixt tables. These are equally follows:

Many-To-Many Relationship: This is what our instance in a higher place 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 production could have many customers. You create a many-to-many relationship by using a third tabular array, called a junction table (more on that below).
Ane-To-Many Relationship: This is the most common relationship type. You lot don't demand a third (junction) table for this type of human relationship. In this type of relationship, a row in tabular array A can accept many matching rows in tabular array B, but a row in table B can take only ane matching row in table A. For example, a row in a Gender tabular array (which contains the records Male and Female) can have many matching rows in a Customers table, but a row in the Customers table can but accept one matching row in the Gender table. That is unless at that place was a business dominion that immune customers to be male person and female at the same time. In this case, a many-to-many relationship would need to be established.
1-To-One Relationship: A row in tabular array A can have just 1 matching row in tabular array B, and vice versa. This is not a common relationship type, as the data in tabular array B could but have hands been in tabular array A. This relationship type is mostly 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 divers.

Junction Tabular array

In the above human relationship, the Orders tabular array is known as a junction table. A junction table is i that contains mutual fields from two or more other tables within the aforementioned database. It is used as a reference table in a many-to-many relationship (such as we are doing in our instance).

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

So if you e'er hear someone mention 1 of those, yous should have some thought what they are referring to.

Primary Central vs Foreign Key

Nosotros already know that a chief key ensures that the data in the field is unique. This is of import because our Orders table needs a unique value in lodge to reference any tape from the other tables. For example, it tin't utilize the FirstName field considering there could exist more than one person with a given first proper name. And it can't utilize the DateCreated value considering it's possible that two records could be created at exactly the same time (eg, if two operators are inbound information or if many records are imported from an external source). This is why nosotros need at least one field that we know will merely e'er comprise a unique value.

A foreign cardinal is merely the primary cardinal's corresponding field in the related tabular array. So in our example, the CustomerId field in the Orders tabular array is a foreign primal, while the CustomerId field in the Customers table is a master central. Likewise, the ProductId field in the Orders table is a foreign primal, 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 go used to. It volition definitely brand the database structure easier to understand - especially when you lot start calculation more tables that also need to have relationships established.

Relationships and Queries

Creating relationships similar the one above can assist in building queries against the database. Next, nosotros're going to use our newly created relationship to aid usa create a query against the tables in our database.