Create Databases and Simple Tables

Creating Relationships Between Tables

In Access, a relationship is an association between common fields in two tables. You can use this association to link the primary key field in one table to a field that contains the same information in another table. The field in the other table is called the foreign key. For example, if customer accounts are assigned to specific sales employees, you can establish a relationship by linking the primary key EmployeeID field in the Employees table with the foreign key EmployeeID field in the Customers table. Each customer account is assigned to only one employee, but each employee can manage many customer accounts, so this type of relationship—the most common—is known as a one-to-many relationship.

Similarly, if every order is associated with a customer, you can establish a relationship by linking the primary key CustomerID field in the Customers table and foreign key CustomerID field in the Orders table. Each order is placed by only one customer, but each customer can place many orders. So again, this is a one-to-many relationship.

Less common relationships include:

• One-to-one. In this type of relationship, each record in one table can have one and only one related record in the other table. This type of relationship isn’t commonly used because it is easier to put all the fields in one table. However, you might use two related tables instead of one to break up a table with many fields, or to track information that applies to only some of the records in the first table.

• Many-to-many. This type of relationship is really two one-to-many relationships tied together through a third table. You might see this relationship in a database that contains Products, Orders, and Order Details tables. The Products table has one record for each product, and each product has a unique ProductID. The Orders table has one record for each order placed, and each record in it has a unique OrderID. However, the Orders table doesn’t specify which products were included in each order; that information is in the Order Details table—the table in the middle that ties the other two tables together. Products and Orders each have a one-to-many relationship with Order Details. Products and Orders therefore have a many-to-many relationship with each other. In plain language, this means that every product can appear in many orders, and every order can include many products.

The most common way of creating a relationship between two tables is to add the tables to the Relationships page displayed when you click the Relationships button in the Relationships group on the Database Tools tab. You then drag a field in one table to the common field in the other table and complete the relationship definition in the Edit Relationships dialog box. In this dialog box, you are given the opportunity to impose a restriction called referential integrity on the data, which means that an entry will not be allowed in one table unless it already exists in the other table.

After you have created a relationship, you can delete it by deleting the line connecting the tables on the Relationships page. You can clear all the boxes from the page by clicking the Clear Layout button in the Tools group on the Relationship Tools Design contextual tab.

In this exercise, you’ll create relationships between one table and two other tables. Then you’ll test the referential integrity of one of the relationships.

1. On the Create tab, in the Tables group, click the Table button to create a new table.

   

   Before we add fields to this table, let’s save it.

2. On the Quick Access Toolbar, click the Save button, name the table Orders, and click OK.

   

3. To the right of Click to Add, click the arrow, and in the data type list, click Number. Repeat this step to create a second field with the Number data type.

4. Double-click Field1, and type CustomerID. Then double-click Field2, and type EmployeeID.

   Each order in the Orders table will be placed by one customer and will be handled by one employee. Let’s create relationships between the Orders table and the Customers and Employees tables so that we don’t create records for orders from customers who don’t exist or that seem to have been handled by employees who don’t exist.

5. Close the Orders table.

   TIP

   You cannot create a relationship for an open table.

6. On the Database Tools tab, in the Relationships group, click the Relationships button.

   

   The Show Table dialog box opens so that you can indicate the tables for which you want to create a relationship.

   TROUBLESHOOTING

   If the dialog box doesn’t open automatically, click the Show Table button in the Relationships group on the Design contextual tab.

   

   The Tables page of the Show Table dialog box.

7. With Customers selected on the Tables page, click Add. Then double-click Orders, and click Close.

   Access displays the Relationships page and adds a Relationship Tools contextual tab to the ribbon.

   

   The two boxes list all the fields in their respective tables.

8. In the Customers field list, click CustomerID, and drag it down and over CustomerID in the Orders field list, releasing the mouse button when two little boxes, one containing a plus sign, appear below the pointer.

   The Edit Relationships dialog box opens.

   

   At the bottom of the dialog box, Access indicates that this will be a one-to-many relationship.

9. Select the Enforce Referential Integrity check box, and then click Create.

   Access creates the link between the primary key in the Customers table and the foreign key in the Orders table, and a line now connects the two field lists on the Relationships page.

   

   The symbols at each end of the line indicate that each Customer ID value appears only once in the Customers table but can appear many times in the Orders table.

10. On the Design contextual tab, in the Relationships group, click the Show Table button. Then in the Show Table dialog box, double-click the Employees table, and click Close.

    

    Access adds a box listing all the fields in the Employees table to the Relationships page.

11. On the page, drag the title bars of the three field lists to arrange them so that they are side by side and equidistant.

12. In the Employees field list, click the EmployeeID field, and drag it down and over the EmployeeID field in the Orders field list. Then in the Edit Relationships dialog box, select the Enforce Referential Integrity check box, and click Create.

13. After Access draws the relationship line between the primary key and the foreign key, close the Relationships page, clicking Yes to save its layout.

14. Open the Orders table. Then in the CustomerID field of the first record, type 11, and click below the record to complete it.

    Access displays a message box telling you that you cannot add the new record to the table.

    

    The value in the CustomerID field in the Orders table must match a value in the primary key CustomerID field in the Customer table.

15. Click OK. Then change the value to 1, and click below the record to complete it.

    This time, Access accepts the value because there is a record with the value 1 in the primary key CustomerID field of the Customers table.