Creating Relationships | Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))

3 4

Chapter 3, "Introduction to Database Design," included a discussion of normalizing database tables and one-to-one, one-to-many, and many-to-many relationships. These relationships are set up in the Relationships window, by dragging a field from one table to another. When you link two tables, you have a number of options for each relationship, which can be selected in the Edit Relationships dialog box that automatically opens when you link tables.

To test creating various types of relationships, you can create a set of tables as described in Table 4-3 through Table 4-7 below (as a time-saver, these tables can be found in the sample database Test Access 2002.mdb), or you can use your own tables, provided they have appropriate matching fields for setting up the relationships.

Table 4-3. tblStudents—For the "One" Side of a One-to-Many Relationship

Field name	Data type
StudentID	Text (key field)
FirstName	Text
MiddleName	Text
LastName	Text
MatriculationDate	Date/Time
DegreeProgram	Text
GraduationDate	Date/Time

Table 4-4. tblClasses—For the "One" Side of a One-to-Many Relationship

Field name	Data type
ClassNo	Text (key field)
ClassName	Text
Program	Text
Credits	Number (Integer)

Table 4-5. tblStudentCharges—For the "Many" Side of a One-to-Many Relationship

Field name	Data type
StudentID	Text (Indexed, Duplicates OK)
Charge	Currency
Description	Text
ChargeDate	Date/Time
Paid	Yes/No

Table 4-6. tblStudentIDs—Typical Second Table for a One-to-One Relationship

Field name	Data type
StudentID	Text (key field)
SSN	Text
MilitaryID	Text
ForeignID	Text

Table 4-7. tblStudentClasses—Linking Table for a Many-to-Many Relationship Between tblStudents and tblClasses

Field name	Data type
StudentID	Text (indexed Duplicates OK)
ClassNo	Text (indexed Duplicates OK)
Semester	Text
Year	Text

Creating a One-to-Many Relationship

One-to-many relationships are by far the most common relationships in Access databases. A one-to-many relationship links a table with one record per key field value to another table that may have multiple records for that same value. In the sample table set, tblStudents has one record per StudentID (its key field), while tblStudentCharges has multiple records per StudentID (which will be the foreign key field in the relationship). To set up a one-to-many relationship between these tables, follow the steps below:

Open the Relationships window by clicking the Relationships button on the toolbar, or selecting Relationships from the Tools menu.
Click the Show Table button in the toolbar to open the Show Table dialog box.
Add tblStudents and tblStudentCharges (or two of your own tables with an appropriate matching field).

tip

Amatching field can have different names in the two tables, but it must be of the same data type, with one exception: An AutoNumber field matches a Number (Long Integer) field.
Drag the linking StudentID field from the tblStudents table to the StudentID field of the tblStudentCharges table. The Edit Relationships dialog box opens, as shown in Figure 4-23.
Access detects the appropriate relationship type (One-To-Many) and lists it in the Relationship Type box at the bottom of the Edit Relationships dialog box.

Figure 4-23. The Edit Relationships dialog box lists a one-to-many relationship type when the StudentID field is dragged from tblStudents to tblStudentCharges.
Click the Enforce Referential Integrity check box (see the "Selecting Enforce Referential Integrity" section below for details on this option); click the Create button to create the relationship, which appears as a line with a 1 on the "one" side of the linking line, and an infinity symbol on the "many" side, as shown in Figure 4-24.
Figure 4-24. A one-to-many relationship in the Relationships window is indicated by the 1 and infinity symbol at the ends of the link.

Creating a One-to-One Relationship

A one-to-one relationship links two tables, each with a key field (so they have only one record per key field value). Typically, two tables are linked in a one-to-one relationship because some of the data is more confidential, or is used less frequently. In the sample table set, tblStudents and tblStudentIDs both have the StudentID field as the key field; tblStudentIDs stores various IDs, and is kept confidential. To set up a one-to-one relationship between these tables (or two suitable tables of your own), follow the steps below:

Open the Relationships window and add tblStudents and tblStudentIDs to the diagram.
Drag the linking StudentID field from the tblStudents table to the StudentID field of the tblStudentIDs table; the Edit Relationships dialog box opens, with the relationship type listed as One-To-One.
Click the Enforce Referential Integrity check box and then click Create to create the relationship, which is indicated by a 1 at each end of the linking line in the Relationships window, as shown in Figure 4-25.
Figure 4-25. A one-to-one relationship is indicated by a 1 at each end of the link in the Relationships window.

Creating a Many-to-Many Relationship

A many-to-many relationship is a pair of one-to-many relationships, with a linking table between the two tables to be linked. The linking table needs only the two fields that are key fields in the two main tables, though it may contain other fields as well. In the sample table set, tblStudentClasses is a linking table that can be used to set up a many-to-many relationship between tblStudents and tblClasses; it contains tblStudentID, the key field of tblStudents, and tblClassNo, the key field of tblClasses. In tblStudentClasses, these fields are not key fields (they will be the foreign keys in the two one-to-many relationships); they are both Indexed, Duplicates OK. Follow the steps below to set up the many-to-many relationship.

Open the Relationships window, and add tblStudents, tblClasses, and tblStudentClasses to the diagram (or your own tables with appropriate matching fields).
Drag the StudentID field from tblStudentClasses to tblStudents to set up the first of the two one-to-many links that comprise the many-to-many link; as before, select the Enforce Referential Integrity check box and click Create to create the one-to-many relationship.
Similarly, drag the ClassNo field from tblStudentClasses to tblClasses to set up the second one-to-many link; select the Enforce Referential Integrity check box and click Create to create this one-to-many relationship. Figure 4-26 shows the finished many-to-many relationship in the Relationships window.
Figure 4-26. A many-to-many relationship consists of two one-to-many relationships with a linking table at the "many" end of both.

After you have set up a relationship in the Relationships window, the link will automatically appear in the Query Design view when you add the linked tables to a query.

Selecting Enforce Referential Integrity

If the Enforce Referential Integrity check box is selected in the Edit Relationships dialog box, Access uses a set of rules to ensure that relationships between records in related tables are valid and that you don't accidentally delete or change related data. To enforce referential integrity, the following requirements must be met:

The matching field in the primary table must be a primary key or have a unique index.
The related fields must have the same data type, with two exceptions: an AutoNumber field can match a Long Integer field, and an AutoNumber field with a field size of Replication ID can match a Number field with a field size of Replication ID.
Both tables must be in the same database. For linked tables, you must set the relationship in the original database.

tip

It's best to set up relationships and enforce referential integrity as soon as possible after creating tables, to avoid problems with trying to set up a relationship with referential integrity when existing data in the tables conflicts with referential integrity rules.

Rules for Enforcing Referential Integrity

Once you choose to enforce referential integrity in your tables, there are certain things you can and cannot do as you enter data. Here are some ways in which your data entries will be checked for accuracy:

You can't enter a value in the foreign key field of the related table unless there's a matching value in the primary key of the primary table. For example, you can't enter an order for a Customer ID that doesn't exist in the Customers table.
You can't delete a record from a primary table if there's a matching record in the related table. For example, you can't delete a Customer record if there's an Order record for that customer, unless you have checked the Cascade Delete Related Records option in the Edit Relationships dialog box. (See the "Selecting Cascade Delete Related Records" section later in this chapter for details on this option.)
You can't change a primary key value in the primary table if there are matching records in the related table, unless you have checked the Cascade Update Related Fields option in the Edit Relationships dialog box. (See the "Selecting Cascade Update Related Fields" section later in this chapter for details on this option.)

Fixing Referential Integrity Errors

While you're setting up relationships between tables (especially if you're working on tables in an inherited database, in which the data might not have been normalized), you might see an error message similar to the one shown in Figure 4-27 when you try to join two tables in a one-to-many relationship. This error message popped up when I tried to create a link between tblBooksSpecs and tblBooksAndAuthors, in an earlier version of the Crafts database, when the database was being normalized.

figure 4-27. an error message indicates that an attempted link between tables violates referential integrity.

Figure 4-27. An error message indicates that an attempted link between tables violates referential integrity.

This error resulted from records in tblBookSpecs that had a BookID value with no matching record in tblBooksAndVideos. This could happen if a book (and one or more specialties for that book) was entered and then later deleted. Without referential integrity enforced, a deletion such as this can result in orphaned records-records in the "many" table that don't have matching records in the "one" table.

Fortunately, Access includes a handy tool for debugging linking problems: the Find Unmatched Query Wizard query. To fix a referential integrity error, follow these steps, which use a set of test tables from the Test Access 2002.mdb database, containing old Crafts data that violates referential integrity rules.

In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
In the New Query dialog box, shown in Figure 4-28, select Find Unmatched Query Wizard and click OK.
Figure 4-28. Select Find Unmatched Query Wizard to create a new query to track down records causing a referential integrity error.
Proceed through the pages of the wizard, selecting first the table that has unmatched data (tblBooksAndVideos is the test table for this purpose), followed by the table that contains the related records (tblPublishers), and then finally the linking field (Pubcode). This last step is shown in Figure 4-29.
Figure 4-29. Set the linking field for the Find Unmatched Query Wizard on the third page of the wizard.
On the next wizard page, select the fields you want to see in the results (ID, Title, and Pubcode).
On the final page, either accept or change the suggested query name (it's just a temporary query that will be deleted after the data is cleaned up), and click Finish.

Figure 4-30 shows a typical Find Unmatched query in Datasheet view, listing the Pubcode records in tblBooksAndVideos that don't have matching records in tblPublishers. To fix this problem, select the correct Pubcode value from the tblPublishers table for each record in tblBooksAndVideos (this has already been done in the Crafts database, so that referential integrity could be enforced).

As an example, Body Blueprint was a typo (it should have been "Body Blueprints"), so I edited it. Another error was caused by a publisher name that wasn't in tblPublishers; I added Liveright to tblPublishers so it would have a match. I also did a search and replace to replace Schoolhouse Press with Schoolhouse (the Publisher Code for that entry in tblPublishers). These specific cases are typical of the data errors that will cause the referential integrity error message.

Whatever the cause of the referential integrity error, after either deleting or editing the unmatched records, you should be able to set up a one-to-many relationship between the two tables without triggering the error message.

figure 4-30. when you open the query created by the find unmatched query wizard in datasheet view, you can see which records caused the error.

Figure 4-30. When you open the query created by the Find Unmatched Query Wizard in Datasheet view, you can see which records caused the error.

Selecting Cascade Update Related Fields

If referential integrity is enforced, selecting the Cascade Update Related Fields check box in the Edit Relationships dialog box specifies that when you change the primary key of a record in the primary table, Access will change the primary key to the new value in all related records. For example, if a Customer ID is changed in the Customers table, the Cus tomer ID value for all orders for that customer in the Orders table will be changed to match. It's generally a good idea to select this option.

Selecting Cascade Delete Related Records

If referential integrity is enforced, selecting the Cascade Delete Related Records check box in the Edit Relationships dialog box specifies that when you delete a record in the primary table, all the related records in the linked table will also be deleted.

Troubleshooting - I don't want to delete orphaned related records

Checking Cascade Deleted Records in the Edit Relationships dialog box can be dangerous, because in some cases (such as running a delete query), the related records can be deleted without warning. Don't select this option if you don't want to automatically delete linked records. This would be the case if you need to retain these records for archival purposes, or you may want to reassign them to another record in the "one" table.

Specifying Join Type

When you want to create a join between two tables, you are given a number of options for the way in which Access will process queries related to that relationship. You can select and modify the way in which tables are joined by clicking the Join Type button in the Edit Relationships dialog box, or by double-clicking the join line between tables in the Query Designer. The Join Properties dialog box opens, and you can choose one of three options:

Include only the rows where joined fields in both tables are equal. (This means that there is a record with a matching value in both the tables.)
Include all the records from the selected table, and only those from the related table in which the joined fields are equal. (This means that records from the selected table will be included even if they don't have a matching value in the linking field.)
Include all the records from the related table, but only those from the selected table in which the joined fields are equal. (This means that records from the related table will be included even if they don't have a matching value in the linking field.)

These choices are easier to explain in a query, where you can look at the query results, so let's make a simple query in the Test Access 2002 database to see the difference in returned records for the three options.

Create a new query in Design view and add tblContacts and tblMeetings to it. A new join line appears, based on the matching ContactID field. Since this join is created in a query, it doesn't have referential integrity enforced.
Add ContactID, LastName, MeetingDate and Subject to the query grid, and save the query as qryContactMeetings.
Double-click the relationship line to open the Join Properties dialog box as shown in Figure 4-31.
Figure 4-31. Select an option for combining data from two tables in the Join Properties dialog box.
Make sure option 1 is selected, which indicates that the query results include only records where the joined fields from both tables are equal, and click OK.
Switch to Datasheet view and note that there are four records; these are the records where there is a matching value in the ContactID field in both tblContacts and tblMeetings.
Switch back to Design view, open the Join Type dialog box again, select option 2, and then click OK.
Switch to Datasheet view and you'll see that there are now 501 records. The extra records represent employees who don't have a matching record in tblMeetings.
Finally, switch back to Design view, open the Join Type dialog box, and select option 3; this time, when you switch back to Datasheet view, there are seven records, three of which are meeting records that don't have a matching contact record (this would not be possible if referential integrity was enforced for this relationship).

Which option you choose depends on whether you want to see records from one table that don't have a match in the other table, or only records that have a matching record in both tables.