Microsoft Office Professional 2010 Step by Step eBook

Creating Relationships Between Tables 815

Tip The coverage of relationships in this topic is deliberately simple. However, relationships

are what make relational databases tick, and Access provides a number of fairly complex

mechanisms to ensure the integrity of the data on either end of the relationship. For a good

overview, search for Guide to table relationships in Access Help.

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.

SET UP You need the MyTables database you worked with in the preceding exercise

to complete this exercise. Open the MyTables database, and then follow the steps.

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.