Section 5.2. Using a Relationship | Access 2007[c] The Missing Manual

5.2. Using a Relationship

The relationship between Dolls and Manufacturers is implicit , which is a fancy way of saying that you know the relationship exists, but Access doesn't. Database pros aren't satisfied with this arrangement. Instead, they almost always define their relationships explicitly . When you create an explicit relationship, you clearly tell Access how two tables are related . Access then stores the information about that relation-ship in the database file.

You have good reasons to bring your relationships out into the open . Once Access knows about a relationship, it can enforce better error checking. It can also provide handy features for browsing related data and editing linked fields. You'll see all these techniques in the following sections. But first, you need to learn how to define a relationship.

5.2.1. Defining a Relationship

You can try out the following steps with the Bobblehead.accdb file, which is included with the online examples for this chapter. It contains the Dolls and Manufacturers tables, in their original form (with no relationships defined). The BobbleheadRelationships.accdb database file shows the final product: two tables with the right relationship.

Here's what you need to do to set up a relationship:

Every relationship links two fields, each in a different table. Your first step is to identify the field you need to use in the parent table .

In a well-designed database, you use the primary-key field (Section 2.4) in the parent table. For example, in the Manufacturers table, you use the ID column, which uniquely identifies each manufacturer.
Open the child table in Design view. (The quickest way is to right-click it in the navigation pane, and then choose Design View.)

In this example, the child table is Dolls.

Create the field you need in the child table, if it's not there already .

Each child record creates a link by storing a piece of information that points to a record in the parent table. You need to add a new field to store this information, as shown in Figure 5-1.

Note: The fields that you link in the parent and child tables must have consistent data types. However, there's one minor wrinkle. If the parent field uses the AutoNumber data type, then the child field should use the Number data type instead (with a Field Size of Long Integer). Behind the scenes, an AutoNumber and a Long Integer actually store the same numeric information. But the AutoNumber data type tells Access to fill in the field with a new, automatically generated value whenever you create a record. You obviously don't want this behavior for the ManufacturerID field in the Dolls table.

Figure 5-1. In the Dolls table, you need a field that identifies the manufacturer for that doll. It makes sense to add a new field named ManufacturerID. Set the data type to Number, and the Field Size to Long Integer, so it matches the ID field in the Manufacturers table. After you add this field, you need to fill it with the right information. (Each doll record should have the ID number of the corresponding manufacturer.)

Close both tables .

Access prompts you to save your changes. Your tables are now relationship-ready.
Choose Database Tools Show/Hide Relationships .

Access opens a new tab named Relationships. This tab's a dedicated window where you can define the relationships between all the tables in your database. In this example, you'll create a just a single relationship, but you can use the Relationships tab to define many more.

Before Access lets you get to work in the Relationships tab, it pops up a Show Table dialog box asking what tables you want to work with (see Figure 5-2).

Figure 5-2. You can add as many tables as you want to the Relationships tab. Be careful not to add the same table twice (it's unnecessary and confusing).
Add both the parent table and child table to your work area .

It doesn't matter which one you choose first. To add a table, select it in the list, and then click Add (or just double-click it).

Access represents each table in the Relationships tab by a small box that lists all the table fields. If relationships are already defined between these tables, they'll appear as connecting lines.

Click Close .

You can now arrange the tables in the Relationships tab (see Figure 5-3). The Relationships tab shows a database diagram it's the canvas where you add relationships by "drawing" them on.

Figure 5-3. You can drag the tables you've added to any place in the window. If you have a database that's thick with relationships, this ability lets you arrange them so that the relationships are clearly visible. To remove a table from the diagram, right-click it, and then choose Hide Table. To add another table, right-click the blank space, and then choose Show Table to pop up the Show Table dialog box.

Tip: Access gives you a shortcut if you need to rework the design of a table that's open in the Relation-ships tab. Just right-click the table box, and choose Design Table.

To define your relationship, find the field you're using in the parent table. Drag this field to the field you want to link it to in the child table .

In this case, you're linking the ManufacturerID field in the Dolls table (the child) to the ID field in the Manufacturers table (the parent). So drag ManufacturerID (in the Dolls box) over to ID (in the Manufacturers box).

Tip: You can drag the other way, too (from the child to the parent). Either way, Access creates the same relationship.

When you release the mouse button, the Edit Relationships dialog box appears (see Figure 5-4).

Figure 5-4. Access is clever enough to correctly identify the parent table (shown in the Table/Query box) and the child table (shown in the Related Table/Query box) when you connect two fields. Access identifies the field in the parent table because it has a primary key (Section 2.4) or a unique index (Section 4.1.3). If something isn't quite right in the Edit Relationships dialog box, then you can swap the tables or change the fields you're using to create the relationship before continuing.

If you want to prevent potential errors, then put a checkmark in the Enforce Referential Integrity option. (It's always a good idea.)

This setting turns on enhanced error checking, which prevents people from making a change that violates the rules of a relationship (like creating a doll that points to a nonexistent manufacturer). You'll learn more about referential integrity and the two settings for cascading changes in Section 5.2.3. For now, it's best to switch on the Enforce Referential Integrity option and leave the others unchecked.
Click Create .

This action creates the relationship that links the two tables. It appears in the diagram as a line (Figure 5-5).

Tip: If you chose Enforce Referential Integrity (in step 9), Access checks to make sure any existing data in the table follows the relationship rules. If it finds some that doesn't, then it alerts you about the problem and refuses to continue. At this point, the best strategy's to create the relationship without referential integrity, correct the invalid data, and then edit the relationship later to turn on referential integrity.

Close the Relationships tab. (You can click the X in the tab's top-right corner, or choose Relationship Tools Design Relationships Close.)

Access asks whether or not you want to save the Relationships tab's layout. Access is really asking you whether you want to save the relationship diagram you've created. No matter what you choose, the relationship remains in the database, and you can use it in the same way. The only difference is whether you'll be able to quickly review or edit the relationship in the Relationships tab.

Figure 5-5. Access uses a line to connect related fields in the Relationships tab. The tiny 1 and infinity ( ) symbols let you distinguish between the "one" and the "many" side in this one-to-many relationship. To edit the relationship, double-click the line. To remove it altogether, right-click the line, and then choose Delete.

If you choose to keep the relationship diagram, the next time you switch to the Relationships tab (by choosing Database Tools Show/Hide Relationships), you see the same arrangement of tables. This features handy.

If you choose not to keep the relationship diagram, it's up to you to recreate the diagram next time by adding the tables you want to see and arranging them in the window (although you won't need to redefine the relationships). This process takes a little more work.

Tip: Many database pros choose to save their database diagram, because they want to see all their relationships at once in the Relationships tab, just the way they left them. However, real-world databases often end up with a tangled web of relationships. In this situation, you may choose not to save a complete diagram so you can focus on just a few tables at once.

5.2.2. Editing Relationships

The next time you want to change or add relationships, you'll follow the same path to get to the Relationship window (choose Database Tools Show/Hide Relationships).

If you choose to save a relationship diagram (in step 11 in the previous section), the tables you added appear automatically, just as you left them. If you want to work with tables that aren't in any relationships yet, you can add them to the diagram by right-clicking anywhere in the blank area, and then choosing Show Table.

If you chose not to save your relationship diagram, you can use a few shortcuts to put your tables back on display:

Drag your tables right from the navigation pane, and then drop them in the Relationships tab.
Choose Relationship Tools Design Relationships All Relationships to show all the tables that are involved in any relationships you've created previously.
Add a table to the diagram, select it, and then choose Relationship Tools Design Relationships Direct Relationships to show the tables that are linked to that table.

As you already know, you can use the Relationships tab to create new relationships. You can also edit the relationships you've already created. To do so, right-click the line that represents the relationship, and then choose Edit Relationship. (This takes some nimble finger-clicking. If you don't see the Edit Relationships option in the menu, you've just missed the line.) To remove a relationship, right-click the relationship line, and then choose Delete.

Note: Usually, you edit a relationship to change the options for referential integrity, which you'll learn about in the next section.

5.2.3. Referential Integrity

Now that you've gone to the work of defining your relationship, it's time to see what benefits you've earned. As in the real world, relationships impose certain restrictions. In the database world, these rules are called referential integrity . Taken together, they ensure that related data's always consistent.

Note: Referential integrity comes into action only if you switched on the Enforce Referential Integrity option (Section 5.2.1) for your relationship. Without this detail, you're free to run rampant and enter inconsistent information.

In the bobblehead example, referential integrity requires that every manufacturer you refer to in the Dolls table must exist in the Manufacturer table. In other words, there can never be a bobblehead record that points to a nonexistent manufacturer. That sort of error could throw the hardiest database software out of whack.

To enforce this rule, Access disallows the following three actions:

Adding a bobblehead that points to a nonexistent manufacturer.
Deleting a manufacturer that's linked to one or more bobblehead records. (Once this record's removed, you're left with a bobblehead that points to a nonexistent manufacturer.)
Updating a manufacturer by changing its ID number, so that it no longer matches the manufacturer ID in the linked bobblehead records. (This updating isn't a problem if you use an AutoNumber field, because you can't change AutoNumber values once you've created the record.)

Note: If you need to add a new doll made by a new manufacturer, you must add the manufacturer record first, and then add the doll record. There's no problem if you add manufacturer records that don't have corresponding doll recordsafter all, it's perfectly reasonable to list a manufacturer even if you don't have any of the dolls they've made.

Along with these restrictions, Access also won't let you remove a table if it's in a relationship. You need to delete the relationship first (using the Relationships window) and then remove the table.

5.2.3.1. Blank values for unlinked records

It's important to realize that there's one operation you can perform that doesn't violate referential integrity: creating a bobblehead that doesn't point to any manufacturer. You do this by leaving the ManufacturerID field blank (which database nerds refer to as a null value ). The only reason you'll leave the ManufacturerID field blank is if the manufacturer record doesn't exist in your database, or if the information doesn't apply. Perhaps the bobblehead wasn't created by any manufacturer but was created by an advanced space-faring alien race and left on this planet for you to discover.

If this blank-value back door makes you nervous, then you can stop it. Just set the Required field property (Section 4.1.1) on the ManufacturerID field in the Dolls table. This setting ensures that every bobblehead in your Dolls table has legitimate manufacturer information. This technique's important when related information isn't optional. A sales company shouldn't be able to place an order or create an invoice without linking to the customer who made the order.

5.2.3.2. Cascading deletes

The rules of referential integrity stop you cold if you try to delete a parent record (like a manufacturer) that other child records (like dolls) link to. However, there's another optionand it's much more drastic. You can choose to blow away all related child records whenever you delete a parent. For example, this would allow you to remove a manufacturer and wipe out all the dolls that were produced by that manufacturer.

Warning: Cascading deletes are risky. It's all too easy to wipe out way more records that you intend, and if you do there's no going back. Even worse , the Undo feature can't help you reverse this change. So proceed with caution.

FREQUENTLY ASKED QUESTION
Switching Off Referential Integrity

Are there any situations where you don't want to enforce referential integrity ?

In most cases, referential integrity's the ultimate database safety check, and no one wants to do without itespecially if the database includes mission-critical information for your business. Remember, referential integrity prevents only inconsistent data. It still lets you leave a field blank if there's no related record that you want to link to.

The only time you may decide to dodge the rules of referential integrity is when you're using partial copies of your database. This situation usually happens in a large business that's using the same database at different sites.

Consider an extremely successful pastry sales company with six locations. When a customer makes an order at your downtown location, you add a new record in the Orders table, and fill in the CustomerID (which links to a full record in the Customers table). But here's the problem. The full customer record may not be in your copy of the databaseinstead, it's in one of the databases at another site, or at company headquarters. Although the link in the Orders table's valid, Access assumes you've made a mistake because it can't find the matching customer record.

In this situation, you may choose to turn off referential integrity so you can insert the record. If you do, then be sure to enter the linked value (in this case, the CustomerID) very carefully to avoid errors later on.

To turn on this option, you need to switch on the Cascade Delete Related Records setting when you create your relationship (Figure 5-4). You can also modify the relationship later on to add this setting.

Once you've switched this option on, you can try it out by deleting a manufacturer, as shown in Figure 5-6.

Figure 5-6. In this example, the Dolls-Manufacturers relationship uses the Cascade Delete Related Records setting. When you delete a manufacturer, Access warns you that you'll actually end up deleting every linked doll record, for a total of nine records.

5.2.3.3. Cascading updates

Access also provides a setting for cascading updates. If you switch on this feature (by going to the Edit Relationships dialog box, and then choosing Cascade Update Related Fields), Access copies any change you make to the linked field in the parent record to all the children.

With the bobblehead database, a cascading update lets you change the ID of one of your manufacturers. When you change the ID, Access automatically inserts the new value into the ManufacturerID field of every linked record in the Dolls table. Without cascading updates, you can't change a manufacturer's ID if there are linked doll records.

WORD TO THE WISE
Use Cascading Deletes with Care

Cascade Delete Related Records is the nuclear option of databases, so think carefully about whether it makes sense for you. This setting makes it all too easy to delete records when you should really be changing them.

If you're dropping a customer from your customer database, then it doesn't make sense to remove the customer's payment history, which you need to calculate your total profit. Instead, you're better off modifying the customer record to indicate that this record isn't being used anymore. You could add a Yes/No field named Active to the customer record, and set this field to No to flag customer accounts that aren't currently in use, without removing them.

You should also keep in mind that cascading deletes are just a convenience. They don't add any new features. If you don't switch on Cascade Delete Related Fields, you can still remove linked records, as long as you follow the correct order. If you want to remove a manufacturer, then start by removing any linked bobbleheads, or changing those bobbleheads to point to a different manufacturer (or have no manufacturer at all) by modifying the ManufacturerID values. Once you've taken this step, you can delete the manufacturer record without a problem.

Cascading updates are safer than cascading deletes, but you rarely need them. That's because if you're following the rules of good database design, you're linking based on an AutoNumber ID column (Section 2.3.9). Access doesn't let you edit an AutoNumber value, and you don't ever need to. (Remember, an AutoNumber simply identifies a record uniquely, and it doesn't correspond to anything in the real world.)

On the other hand, cascading updates come in handy if you're working with a table that hasn't been designed to use AutoNumber values for links. If the Dolls and Manufacturers table were linked based on the manufacturer name, then you need cascading updatesit makes sure that child records are synchronized whenever a manufacturer name 's changed. Cascading updates are just as useful if you have linked records based on Social Security numbers, part numbers, serial numbers , or other codes that aren't generated automatically and are subject to change.

5.2.4. Navigating a Relationship

Relationships aren't just useful for catching mistakes. Relationships also make it easier for you to browse through related data. In Chapter 6, you'll learn to create search routines that pull together information from related tables (Section 6.3). But even without this technique, Access provides some serious relationship mojo in the datasheet.

Here's how it works. If you're looking at a parent table in the datasheet, then you can find the related child records for any parent record by clicking the plus box that's just at the left of the row (Figure 5-7).

Figure 5-7. Curious to find out what dolls you have from MagicPlastic? Just click the plus box (circled).

This drops a subdatasheet into view, which shows just the related records (Figure 5-8). You can use the subdatasheet to edit the doll records here in exactly the same way as you would in the full Dolls datasheet. You can even add new records.

Figure 5-8. The subdatasheet's really a filtered version of the ordinary Dolls datasheet. It shows only the records that are linked to the manufacturer you chose. The subdatasheet has all the same view settings (like font, colors, column order) as the datasheet for the related table.

Note: You can open as many subdatasheets as you want at the same time. The only limitation's that the records in a subdatasheet don't show up if you print the datasheet (Section 3.4).

A parent table may be related to more than one child table. In this case, Access gives you a choice of what table you want to use when you click the plus box. Imagine you've created a Customers table that's linked to a child table of customer orders (Orders), and a child table of billing information (Invoices). When you click the plus box, Access doesn't know which table to choose, so it asks you (see Figure 5-9).

Figure 5-9. When Access doesn't know which table to use as a subdatasheet, it lets you pick from a list of all your tables. In this case, only two choices make sense. Choose Orders to see the customer's orders, or Invoices to see the customer's invoices. When you select the appropriate table in the list, Access automatically fills in the linked fields in the boxes at the bottom of the window. You can then click OK to continue.

Note: You have to choose the subdatasheet you want to use only once. Access remembers your setting and always uses the same subdatasheet from that point on. If you change your mind later on, you'll need to tweak the table settings, as described in the box "Changing Subdatasheet Settings" in Section 5.2.5.

As you create more elaborate databases, you'll find that your tables are linked together in a chain of relationships. One parent table might be linked to a child table, which is itself the parent of another table, and so on. This complexity doesn't faze Accessit lets you drill down through all the relationships (see Figure 5-10).

Figure 5-10. There are two relationships at work here. Customers is the parent of Orders (which lists all the orders a customer's placed). Orders is the parent of OrderDetails (which lists the individual items in each order). By digging through the levels, you can see what each customer bought.

POWER USERS' CLINIC
Changing Subdatasheet Settings

You can tweak a few more details that affect how sub- datasheets are shown for your table. To show these settings, switch your table to Design view. Then, choose Table Tools Design Show/Hide Property Sheet ( assuming the Property Sheet isnt currently visible). This action shows the Property Sheet box at the right side of the window.

The Property Sheet has a collection of miscellaneous settings that apply to your whole table. Here are the ones that relate to subdatasheets:

Subdatasheet Name . The linked table used for the subdatasheet. If you have several linked tables, you may choose to adjust this to the one you want to work with. Or set it to (Auto) so that Access prompts you for the subdatasheet you want to use the next time you click the plus box, as shown in Figure 5-9.
Subdatasheet Height . Sets the height, in inches, given to the subdatasheet to display its data. If all the related rows don't fit into this space, then you'll need to scroll through them. The standard setting's 0, which allows the subdatasheet to take as much space as it needs.
Subdatasheet Expanded . Lets you choose whether the subdatasheets should start off hidden until you click the plus box (the default setting), or automatically expand when you open the table (choose Yes).

5.2.5. Lookups with Related Tables

So far, you've seen how relationships make it easier to review and edit your records. But what about when you add your records in the first place? Relationships are usually based on an unhelpful AutoNumber value. When you create a new doll, you probably won't know that 3408 stands for Bobelle House O' Dolls. Access stops you from entering a manufacturer ID that isn't linked to anyone at all, but it doesn't help you choose the ID value you want.

Fortunately, Access has a technique to help you out. In the previous chapter, you learned about lookups (Section 4.4.1), a feature that provides you with a list of possible values for a column. When creating a lookup, you can supply a list of fixed values, or you can pull values from another table. You could create a lookup for the ManufacturerID field in the Dolls table that uses a list of ID values drawn from the Manufacturers table. This type of lookup helps a bitit gives you a list of all the possible values you can usebut it still doesn't solve the central problem. Namely, the befuddled people using your database won't have a clue what ID belongs to what manufacturer. You still need a way to show the manufacturer name in the lookup list.

Happily, lookup lists provide just this feature. The trick's to create a lookup that has more than one column. One column holds the information (in this case, the manufacturer name) that you want to display to the person using the database. The other column has the data you want to use when a value's picked (in this case, the manufacturer ID).

Note: Access is a bit quirky when it comes to lookups. It expects you to add the lookup, and then the relationship. (In fact, when you set up a lookup that uses a table, Access creates a relationship automatically .) So if you've been following through with the examples on your own, then you'll need to delete the relationship between the Dolls and Manufacturers tables (as described in Section 5.2.3) before you go any further.

The following steps show how you can create a lookup list that links the Dolls and Manufactures tables:

Open the child table in Design view .

In this example, it's the Dolls table.
Select the field that links to the parent table, and, in the Data Type column, choose the Lookup Wizard option .

In this example, the field you want is ManufacturerID.
Choose "I want the lookup column to look up the values in a table or query" and then click Next .

The next step shows a list of all the tables in your database, except the current table.
Choose the parent table, and then click Next .

In this case, you're after the Manufacturers table. Once you select it and move to the next step, you'll see a list of all the fields in the table.

Add the field you use for the link and another more descriptive field to the list of Selected Fields (Figure 5-11). Click Next to continue .

In this case, you need to add the ID field and the Manufacturer field.

Figure 5-11. The secret to a good lookup is getting two pieces of information: the primary key (in this case, the ID field) and a more descriptive value (in this case, the manufacturer's name). The ID field's the piece of information you need to store in the doll record, while the Manufacturer field's the value you'll show in the lookup list to make it easier to choose the right manufacturer.

Tip: In some cases, you might want to use more than one field with descriptive information. For example, you might grab both a FirstName and LastName field from a FamilyRelatives table. But don't add too much information, or the lookup list will become really wide in order to fit it all in. This looks a bit bizarre.

Choose a field to use for sorting the lookup list (Figure 5-12), and then click Next .

In this example, the Manufacturer field's the best choice to sort the list.

Figure 5-12. It's important to sort the lookup list, so that the person using it can find the right item quickly. One links students to classes, and the other links teachers to classes.

The next step shows a preview of your lookup list (Figure 5-13). Make sure the "Hide key column" option's selected, and then click Next .

Although the primary-key field has the value that links the two tables together, it doesn't mean much to the person using the database. The other, descriptive field's more important.

Figure 5-13. Here, the lookup list shows the manufacturer name (the Manufacturer field) and hides the manufacturer ID (the ID field).
Choose a name for the lookup column .

Usually, it's clearest if you keep the name of the field that uses the lookup (in this case, ManufacturerID).

The final step also gives you an option named Allow Multiple Values. If you check this, then the lookup list shows a checkbox next to each item, so that you can pick several at once. (In this example, you can create a doll that has more than one manufacturer.) You'll learn more about the Allow Multiple Values option in Section 5.3.2.2.
Click Finish .

Now, Access creates the lookup for the field and prompts you to save the table. Once you do, Access creates a relationship between the two tables you've linked with your lookup column. Here, Access creates a parent-child relationship between Manufacturers and Dolls, just as you did yourself in Section 5.2.1.

Note: The relationships that Access creates don't enforce referential integrity, because Access doesn't know if your records can live up to that strict standard. You can have a doll that points to a nonexistent manufacturer. If this possibility seems dangerously lax, you can edit your relationship using the Relation-ships tab (as described in Section 5.2.2). Begin by adding both the Dolls and the Manufacturers table to the relationships diagram. Then, right-click the relationship line in between, and then choose Edit Relation-ship. Finally, switch on the Enforce Referential Integrity checkbox, and then click OK.

Now, if you switch to the design view of the Dolls table, you can use your lookup when you're editing or adding records (Figure 5-14).

Figure 5-14. Even though the Dolls table stores an ID value in the ManufacturerID field behind the scenes, that's not how it appears on your datasheet. Instead, you see the related manufacturer name (both onscreen and in any printouts you make). Even better, if you need to add a new record or change the manufacturer that's assigned to an existing one, then you can pick the manufacturer from the list by name.