Expert One-on-One Microsoft Access Application Development

The Table Wizard gives you a start at setting up relationships between tables, but it doesn’t do all the work. Even though you specify that a record in one table can match many records in another table, the relationship is not set up as a one-to-many relationship; you need to do this manually, in the Relationships window. I’ll describe the three types of relationships you can create in an Access database, and show you how to set them up in the Relationships window.

Let’s start with some definitions of terms used in creating relationships between tables:

• Primary key. A field (or, less commonly, a set of fields) with a different value (or value combination) for each record in a table. The key field must be unique and can’t be Null.

• Foreign key. A nonunique field in a table that links to the primary key field in another table. In a one-to-many relationships, the primary key is in the “one” table, and the foreign key in the “many” table.

• Cascading update. When referential integrity is enforced, if you change the primary key value in a record in the primary table (for example, EmployeeID in tblEmployees), that value will be changed to match in all the matching records in any related tables. This is generally a good idea.

• Cascading delete. When referential integrity is enforced, if you delete a field in a primary table, all matching records in any related tables are also deleted. This is dangerous, and generally should be avoided.

• Inner join. There must be a matching value in the linked fields of both tables. With an inner join between tblCustomers and tblOrders, for example, you will see only records for customers with orders.

• Left outer join. All records from the left side of the LEFT JOIN operation in a query’s SQL statement are included in the results, even if there are no matching records in the other table. A left outer join between tblCustomers and tblOrders includes all the Customer records, even those with no orders.

• Right outer join. All records from the right side of the RIGHT JOIN operation in a query’s SQL statement are included in the results, even if there are no matching records in the other table. A right outer join between tblMailingListCompanies and tblMailingList includes all the tblMailingList records, even those with no company selected (they won’t have matching records in tblMailingListCompanies).

• Referential integrity. A set of rules that ensures that relationships between records in linked tables are valid and that related data isn’t changed or deleted inappropriately. Setting referential integrity on a link between tblCustomers and tblOrders (on the CustomerID field) would ensure, for example, that you can’t enter a new order without selecting a customer. With referential integrity set, you can’t delete a record from the primary table if there are matching records in the related table (unless you also choose to turn on cascading deletes), and you can’t change the primary key value if there are matching records (unless you also choose to turn on cascading updates).

One-to-Many Relationships

Although Access doesn’t require that linked fields have the same names (only the same data type), to make it easier to match up corresponding fields when setting up relationships, I recommend using the same name for linked primary and foreign key fields.

A one-to-many relationship (by far the most common type of relationship) is needed when a single record in one table can match several records in another table. In the Toy Workshop database, a number of one-to-many relationships are needed; they are listed below, with the “one” (or primary) table on the left and the “many” (or related) table on the right. Some of these relationships are also part of many-to-many relationships, covered below:

• tblCategories—tblToys

• tblCustomers—tblCustomerEmails

• tblCustomers—tblCustomerPhones

• tblCustomers—tblOrders

• tblEmployees—tblRepairs

• tblMailingListCompanies—tblMailingList

• tblMaterials—tblRepairMaterials

• tblMaterials—tblToyMaterials

• tblRepairs—tblRepairMaterials

• tblShippingAddresses—tblOrders

• tblShippingMethods—tblOrders

• tblToys—tblToyMaterials

• tblVendors—tblMaterials

• tblVendors—tblToys

• tblVendors—tblVendorEMails

• tblVendors—tblVendorPhones

If you get an error message “Toy Workshop can’t create this relationship and enforce referential integrity” when trying to create a relationship, this indicates that data in one of the tables violates referential integrity (for example, you might have a tblOrders record without a value in the CustomerID field); fix the data, and you will be able to create the relationship.

Similarly, an error message that says, “Relationship must be on the same number of fields with the same data types” most likely indicates that the fields to be linked are of different data types; change the data type of one field so that it matches the other field (with AutoNumber matching Long Integer), and you should be able to set up the relationship.

As an example of how to set up a one-to-many relationship in the Relationships window (all the others are done similarly), let’s set up the relationship between tblCustomers and tblCustomerPhones. Start by opening the Relationships window and dragging tblCustomers and tblCustomer Phones to it from the Database window (or alternately, selecting them using the Show Table dialog opened from the similarly named toolbar button). Note that the CustomerID field in tblCustomers is bold; that indicates that it is the primary key of this table. The matching CustomerID field in tblOrders is not bold, because it is a foreign key field in that table. To create the join, drag the CustomerID field in tblCustomers to the same-named field in tblOrders, as shown in Figure 1.31.

Figure 1.31

When you release the mouse, the Edit Relationships dialog opens. The Relationship Type box at the bottom of the screen displays the relationship that Access thinks is right; it is usually correct.

If the relationship type you intend to set up isn’t shown in the Relationship Type box in the Edit Relationships dialog—for example, you want to set up a one-to-many relationship, and the box says Indeterminate—the most likely reason is that you have tried to link the wrong fields, or you linked the right fields, but they aren’t of matching data types. Correct the problem and you should see the correct relationship type in the dialog.

In this case, the relationship type is correctly identified as one-to-many, so all you have to do is check Enforce Referential Integrity and Cascade Update Related Fields, and click the Create button, as shown in Figure 1.32.

Figure 1.32

You can now see a line connecting the CustomerID field in tblCustomers to the matching field in tblOrders, as shown in Figure 1.33; note that it has a 1 on the left side (indicating that tblCustomers is the primary or “one” table), and an ∞ sign on the right side (the related or “many” table).

Figure 1.33

One-to-One Relationships

A one-to-one relationship (comparatively rare) is needed when a record in one table can only match a single record in another table. The linking field is the primary key field in both tables. Typically, such a relationship is created to limit access to certain data, such as confidential employee data. In the Toy Workshop sample database, there is a single one-to-one relationship, between tblEmployees and tblEmployeesConfidential. To set up this relationship, drag EmployeeID from tblEmployees to the same field in tblEmployeesConfidential; the Edit Relationships dialog will say One-to-One, as shown in Figure 1.34.

If the Edit Relationships dialog says One-to-Many instead of One-to-One, this indicates that the linking field is not the key field in both tables; change it to the key field in both, and you should be able to set up a one-to-one link.

In the Relationships window, the line representing a one-to-one relationship has a 1 at both ends, as you might expect.

Figure 1.34

Many-to-Many Relationships

A many-to-many relationship is actually a set of two one-to-many relationships. There are two primary tables and a linking table; the linking table has two foreign key fields, one matching the primary key field of each of the primary tables. It may also (but usually doesn’t) contain a few other fields that hold information related to that specific combination of records from the primary tables. In the Toy Workshop database, two many-to-many relationships are needed (the linking table is in the middle of each set):

• tblToys—tblToyMaterials—tblMaterials

• tblRepairs—tblRepairMaterials—tblMaterials

Once you have set up the two one-to-many relationships, you have a many-to-many relationship; Figure 1.35 shows the two many-to-many relationships in the Relationships window. You can see the two sets of primary tables with a linking table in between; tblMaterials serves as the primary table in two many-to-many relationships.

Figure 1.35

If you use a convention of naming all primary and foreign key fields with a suffix of ID, you can easily identify the fields that need to be linked to other tables in the Relationships window. However, not all key fields need to be linked to other tables—MailingListID in tblMailingList doesn’t need any links because there are no tables with multiple records matching one record in tblMailingList.