Establishing Table Relationships | Online Traning Solutions

In Access, you can create relationships between tables so that you can combine the information from more than one table in queries, forms, data access pages, and reports . A relationship is formed by matching the primary-key field in one table with a field containing the same type of information, called the foreignkey field , in another table. The values in the primary-key field must be unique. The values in the foreign-key field need not be unique, but each one should match a value in the corresponding primary-key field. Access provides a method called referential integrity to ensure that the field values match.

Information about

Queries, page 257

You can create these three types of relationships between two database tables: one-to-many, in which a record that is unique in one table can have many corresponding records in the other table; one-to-one, in which a record that is unique in one table is also unique in the other table (a rare occurrence); and manyto-many, in which a record that is unique in one table can have many corresponding records in the other table and vice versa. In this topic, we ll focus on one-to-many relationships.

Information about

Primary Keys, page 217

Creating a One-to-Many Relationship

Let s start by creating a new table called Jobs. Then you ll establish a relationship using this table. Follow these steps:

With Tables selected on the Objects bar, click New on the Database window s toolbar, click Design View , and click OK .
Create the table structure shown in this graphic:
Click the row selector of the JobID field, and click the Primary Key button on the toolbar to make this field the table s primary key.

Assign the fields these properties:

Field	Field Size	Format	Caption	Required
JobID	8		*Job#*	Yes
CustomerID	*Byte*	*000*	*Cust#*	Yes
Name	25
Location	25
Status	25
DateCompleted		*Short Date*	*Date Completed*
AmountInvoice			*Amount of Invoice*

Click the Close button to close the window, and save the new table with the name Jobs when prompted.
To create a relationship between the Customers table and the newly created Jobs table, click the Relationships button on the Access toolbar.

Access opens the Relationships window and displays the dialog box shown in this graphic:
Double-click first Customers and then Jobs to add these tables to the Relationships window, and then close the Show Table dialog box.

The Relationships window looks like the one shown in this graphic:
In the Customers box, click the CustomerID field, hold down the left mouse button, drag the selection to the CustomerID field name in the Jobs box, and release the mouse button.

When you release the mouse button, the Edit Relationships dialog box shown in this graphic appears:

At the bottom of the dialog box, notice that the relationship type is listed as One-To-Many. For each record in the parent table (Customers), there can be many records with a matching field value in the child table (Jobs).
Select the Enforce Referential Integrity check box.

By selecting the check box, you are telling Access to require that values in the CustomerID field in the Jobs table match values in the same field in the Customers table.
Click the Create button.

Access closes the dialog box and draws a line indicating the new relationship, as shown in this graphic:
Close the Relationships window, clicking Yes to save the new relationship when prompted.

Testing Referential Integrity

Now let s test the relationship by adding some data to the Jobs table. Follow these steps:

Open the Jobs table in Datasheet view, and enter the following information, skipping over the Date Completed column:
Try entering an incorrect customer number, such as 999.

You can type any number in the field, but Access won t let you leave the record until a correct customer number is in place.
When you have entered all the data, close the Jobs table.

Editing Related Data

As you create the Jobs table, suppose you realize that you need to track the current job assignments of all employees. You can t add an EmployeeID field to the Jobs table because several employees might be assigned to the same job. Instead, you will have to modify the Employees table to include a JobID field. This process involves changing the design of the Employees table, adding another relationship, and entering the job numbers . Let s do this now:

Open the Employees table, and then click the View button to open the table in Design view.
Click the FirstName field s row selector, and then on the Access toolbar, click the Insert Rows button to add a new blank row above the FirstName field.
Type JobID as the new field name, specify Text as the data type, and assign a field size of 8 and a caption of Job# .
Close the Employees table, saving the changes when you are prompted to do so.
To add another relationship to the database, click the Relationships button on the toolbar.
When the Relationships window opens, click the Show Table button, select Employees , click the Add button, and then click the Close button.
Drag the JobID field in the Jobs box to the JobID field in the Employees box. Then click Enforce Referential Integrity in the Edit Relationships dialog box, and click Create to add this one-to-many relationship to the database.
Close the Relationships window, clicking Yes to save your changes.
Open the Employees table, and enter the job numbers shown in the following graphic, leaving the field for Carol Talbot blank because she works in the office.
Close the table.

Changing or deleting relationships

Editing or deleting relationships is easy in Access. In the relationships window, right-click the lighter middle part of the line indicating the relationship you want to change or delete. Then click Edit Relationship or Delete on the shortcut menu. If you click Edit Relationship, Access displays the Edit Relationships dialog box so that you can make changes.

Viewing Related Data

Access reflects existing table relationships in a way that allows you to see the related information with a single click of the mouse. Follow these steps to check this out:

Open the Customers table in Datasheet view.

A new column on the left contains plus signs, as shown in this graphic:

The plus signs indicate the availability of more data associated with a particular record. The structure is similar to that used in Windows Explorer.
Click the plus sign for Durahomes .

Access displays a subdatasheet of information from the Jobs table, as shown in this graphic:

This subdatasheet contains only the records for Durahomes jobs from the Jobs table. Each of these records also has a plus sign.
Click the plus sign for job 018445A .

Access displays the subdatasheet for this job, which, as you can see in the following graphic, contains records from the Employees table.
Close the Employees subdatasheet by clicking the minus sign for 018445A in the Jobs subdatasheet.

Now let s test the link by making a change in the subdatasheet.
In the 019526A record, select the entry in the Status field, type 50% Shingled , and click the second record to save the change.
Click the minus sign for Durahomes to close the Jobs subdatasheet, and then close the Customers table.
Open the Jobs table, verify that job 019526A reflects the change you just made, and then close the table.