• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint

Defining Relationships

After you have defined two or more related tables, you should tell Microsoft Access how the tables are related. You do this so that Access will be able to link all your tables when you need to use them in queries, forms, data access pages, or reports.

Thus far in this chapter, you have seen how to build the main subject tables of the Contact Tracking database—Companies, Contacts, and Products. Before we define the relationships in the sample database you’ve been building, you need to create a couple of linking tables that define the many-to-many relationships between Companies and Contacts and between Products and Contacts. Table 4-9 on the next page shows you the fields you need for the Company Contacts table that forms the “glue” between the Companies and Contacts tables.

Table 4-9. Field Definitions for the Company Contacts Table
Field NameData TypeDescriptionField Size
CompanyIDNumberCompany/organizationLong Integer
ContactIDNumberPerson within companyLong Integer
PositionTextPerson’s position within the company50
DefaultForContactYes/NoIs this the default company for this contact? 

Define the combination of CompanyID and ContactID as the primary key for this table by clicking the selection button next to CompanyID and then holding down the Ctrl key and clicking the button next to ContactID. Click the Primary Key button on the toolbar to define the key and save the table as CompanyContacts.

Table 4-10 shows you the fields you need to define the Contact Products linking table between the Contacts and Products tables.

Table 4-10. Field Definitions for the Contact Products Table
Field NameData TypeDescriptionField Size
CompanyIDNumberCompany/organizationLong Integer
ContactIDNumberRelated contactLong Integer
ProductIDNumberRelated productLong Integer
DateSoldDate/TimeDate product sold 
SoldPriceCurrencyPrice paid 

As you might remember from Chapter 3, the primary key of the Contact Products table is the combination of CompanyID, ContactID, and ProductID. You can click CompanyID to select it, then hold down the Shift key while you click ProductID (if you defined the fields in sequence) to select all three fields. Click the Primary Key button on the toolbar to define the key, and save the table as ContactProducts.

You need one last table, the Contact Events table, to define all the major tables you’ll need for Contact Tracking as we designed it in Chapter 3. Table 4-11 shows you the fields you need. The primary key for this table is the combination of ContactID and ContactDateTime. Note that we took advantage of the fact that a Date/Time data type in Access can store both a date and a time, so we don’t need two separate date and time fields.

Table 4-11. Field Definitions for the Contact Events Table
Field NameData TypeDescriptionField Size
ContactIDNumberRelated contactLong Integer
ContactDateTimeDate/TimeDate and time of the contact 
ContactNotesMemoDescription of the contact 
ContactFollowUpDateDate/TimeFollow-up date 

Now you’re ready to start defining relationships. To define relationships, you need to return to the Database window by closing any Table windows that are open and then clicking in the Database window to make it active. Choose the Relationships command from the Tools menu to open the relationships definition window. If this is the first time you have defined relationships in this database, Access opens a blank Relationships window and opens the Show Table dialog box, shown in Figure 4-29.

Figure 4-29. The Show Table dialog box.

In the Show Table dialog box, select each table and click the Add button in turn. Click Close to dismiss the Show Table dialog box.

Defining Your First Relationship

If you remember the design work you did in Chapter 3, “Designing Your Database Application,” you know that a company can have several contacts, and any contact can belong to several companies or organizations. This means that companies have a many-to-many relationship with contacts. You should also recall that defining a many-to-many relationship between two tables requires a linking table. Let’s link the Companies and Contacts tables by defining the first half of the relationship—the one between Companies and the linking table, CompanyContacts. You can see that for the CompanyID primary key in the Companies table, there is a matching CompanyID foreign key in the CompanyContacts table. To create the relationship you need, click in the CompanyID field in the Companies table and drag it to the CompanyID field in the CompanyContacts table, as shown in Figure 4-30.

Figure 4-30. Dragging the linking field from the “one” table (Companies) to the “many” table (CompanyContacts).

When you release the mouse button, Access opens the Edit Relationships dialog box, shown in Figure 4-31.

Figure 4-31. The Edit Relationships dialog box.


You can also choose Edit Relationship from the Relationships menu to create a new relationship, but you have to fill in the table and field names yourself. Dragging and dropping does some of this work for you.

You’ll notice that Access has filled in the field names for you. If you need to define a multiplefield relationship between two tables, use the additional blank lines to define those fields (we’ll do that in just a second). Because you probably don’t want any rows created in CompanyContacts for a nonexistent company, click the Enforce Referential Integrity check box. When you do this, Access ensures that you can’t add a row in the CompanyContacts table containing an invalid CompanyID. Also, Access won’t let you delete any records from the Companies table if they have contacts still defined.

Note that after you click the Enforce Referential Integrity check box, Access makes two additional options available: Cascade Update Related Fields and Cascade Delete Related Records. If you select Cascade Delete Related Records, Access deletes child rows (the related rows in the many table of a one-to-many relationship) when you delete a parent row (the related row in the one table of a one-to-many relationship). For example, if you removed a company from the database, Access would remove the related Company Contact rows. In this database design, the CompanyID field has the AutoNumber data type, so it cannot be changed once it is set. However, if you build a table with a primary key that is Text or Number (perhaps a ProductID field that could change at some point in the future), it might be a good idea to select Cascade Update Related Fields. This option requests that Access automatically update any foreign key values in the child table (the many table in a one-to-many relationship) if you change a primary key value in a parent table (the one table in a one-to-many relationship).

You might have noticed that the Show Table dialog box, shown earlier in Figure 4-29, gives you the option to include queries as well as tables. Sometimes you might want to define relationships between tables and queries or between queries so that Access knows how to join them properly. You can also define what’s known as an outer join by clicking the Join Type button in the Relationships dialog box and selecting an option in the Join Properties dialog box. With an outer join, you can find out, for example, which companies have no contacts or which products haven’t been sold.

For details about outer joins, see “Using Outer Joins,” page 287.

Inside Out: Avoid defining a relationship with an outer join

I recommend that you do not define an outer join relationship between two tables. As you’ll learn in Chapter 8, “Building Complex Queries,” Access automatically links two tables you include in a query design using the relationships you have defined. In the vast majority of cases, you will want to include only the matching rows from both tables. If you define the relationship as an outer join, you will have to change the link between the two tables every time you include them in a query.

I also do not recommend that you define relationships between queries or between a table and a query. If you have done a good job of naming your fields in your tables, the query designer will recognize the natural links and define the joins for you automatically. Defining extra relationships adds unnecessary overhead in your database application.

Click the Create button to finish your relationship definition. Access draws a line between the two tables to indicate the relationship. Notice that when you ask Access to enforce referential integrity, Access displays a 1 at the end of the relationship line, next to the one table, and an infinity symbol (∞) next to the many table. If you want to delete the relationship, click the line and press the Delete key.

You now know enough to define the additional one-to-many simple relationships that you need. Go ahead and define a relationship on ContactID between the Contacts and CompanyContacts tables to complete the other side of the many-to-many relationship between Companies and Contacts, a relationship on ContactID between the Contacts and Contact Events tables, and a relationship on ProductID between the Products and ContactProducts tables.

Creating a Relationship on Multiple Fields

There’s one last relationship you need to define in the Contact Tracking database between CompanyContacts and ContactProducts. The relationship between these two tables requires multiple fields from each table. You can start by dragging the CompanyID field from the CompanyContacts table to the ContactProducts table. Access opens the Edit Relationships dialog box.

When you first see the Edit Relationships dialog box for the relationship you are defining between CompanyContacts and ContactProducts, Access shows you only the CompanyID field in the two lists. To complete the relationship definition on the combination of CompanyID and ContactID, you must click in the second line under both tables and select ContactID as the second field for both tables, as shown in Figure 4-32. Select Enforce Referential Integrity as shown and click Create to define the compound relationship.

Figure 4-32. Defining a relationship between two tables using multiple fields.

Figure 4-33 shows the Relationships window for all the main tables in your Contact Tracking database. Notice that there are two linking lines that define the relationship between CompanyContacts and ContactProducts.

Figure 4-33. The Relationships window showing all the main tables in your Contact Tracking database.

If you want to edit or change any relationship, double-click the line to open the Edit Relationships dialog box again. If you want to remove a relationship definition, click on the line linking two tables to select the relationship (the line appears highlighted) and press the Delete key. Access presents a warning dialog box in case you are asking it to delete a relationship in error.

Note that once you define a relationship, you can delete the table or query field lists from the Relationships window without affecting the relationships. To do this, click the table or query list header and press the Delete key. This can be particularly advantageous in large databases that have dozens of tables. You can also display only those tables that you’re working with at the moment. To see the relationships defined for any particular table or query, include it in the Relationships window by using the Show Table dialog box, and then click the Show Direct Relationships button on the toolbar or choose Show Direct from the Relationships menu. To redisplay all relationships, click the Show All Relationships button on the toolbar or choose Show All from the Relationships menu.

When you close the Relationships window, Access asks whether you want to save your layout changes. Click Yes to save the relationships you’ve defined. That’s all there is to it. Later, when you use multiple tables in a query in Chapter 7, “Creating and Working with Simple Queries,” you’ll see that Access builds the links between tables based on these relationships.

Tip: Additional features in the Relationships window

You can right-click any table in the Relationships window and then choose Table Design from the shortcut menu to open that table in Design view. You can also choose Print Relationships from the File menu while viewing the Relationships window to create a report that prints what you laid out in the window.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint