Access 2010: The Missing Manual

Lookups

In a database, minor variations can add up to big trouble. Suppose you’re running International Cinnamon, a multinational cinnamon bun bakery with hundreds of orders a day. In your Orders table, you have entries like this:

Quantity     Product
10           Frosted Cinnamon Buns
24           Cinnamon Buns with Icing
16           Buns, Cinnamon (Frosted)
120          FCBs
...

(Other fields, like the ID column and the information about the client making the order, are left out of this example.)

All the orders shown here amount to the same thing: different quantities of tasty cinnamon and icing confections. But the text in the Product column is slightly different. This difference doesn’t pose a problem for ordinary human beings (for example, you’ll have no trouble filling these orders), but it does create a small disaster if you want to analyze your sales performance later. Since Access has no way to tell that a Frosted Cinnamon Bun and an FCB are the same thing, it treats them differently. If you try to total up the top-selling products or look at long-range cinnamon sales trends, then you’re out of luck.

Note

This example emphasizes a point that you’ve seen before. Namely, databases are strict, no-nonsense programs that don’t tolerate minor discrepancies. For your databases to be useful, you need to make sure you store topnotch information in them.

Lookups are one more tool to help standardize your data. Essentially, a lookup lets you fill a value in a field by choosing from a readymade list of choices. Used properly, this tool solves the problem in the Orders table—you simply need a lookup that includes all the products you sell. That way, instead of typing the product name in by hand, you can choose Frosted Cinnamon Buns from the list. Not only do you save some time, but you also avoid variants like FCBs, thereby ensuring that the orders list is consistent.

Access has two basic types of lookup lists: lists with a set of fixed values that you specify, and lists that are drawn from a linked table. In the next section, you’ll learn how to create the first type. Then, in Chapter 5, you’ll graduate to the second.

Note

The only data types that support lookups are Text and Number.

Creating a Simple Lookup with Fixed Values

Simple lookups make sense if you have a simple, short list that’s unlikely to change. The state prefix in an address is a perfect example. In this case, there’s a set of just 50 two-letter abbreviations (AL, AK, AZ, and so on).

To try out the process in the following list of steps, you can use the Bachelors table included with the online examples for this chapter (look for the DatingService.accdb database file). Or, you can jump straight to the completed lookup by checking out the DatingServiceLookup.accdb file:

1. Open the table in Design view.

   If you’re using the DatingService.accdb example, then open the Bachelors table.

2. Find the field where you want to add the lookup.

   In the Bachelors table, it’s the State field.

3. Make sure your field has the correct data type.

   Text and Number are the most common data types that you’ll use in conjunction with the lookup feature.

4. Choose Lookup Wizard from the data type list.

   This action doesn’t actually change your data type. Instead, it tells Access you want to run the Lookup wizard based on the current data type. When you select this option, the first step of the Lookup wizard appears (Figure 4-17).

   

   Figure 4-17. First you choose the source of your lookup: fixed values or data from another table.

5. Choose “I will type in the values that I want”.

   Lookups with Related Tables describes your other choice: drawing the lookup list from another table.

6. Click Next.

   The second step of the wizard gives you the chance to supply the list of values that should be used, one per row (Figure 4-18). In this case, it’s a list of abbreviations for the 50 U.S. states.

   You may notice that you can supply multiple columns of information. For now, stick to one column. You’ll learn why you may use more on Lookups with Related Tables.

   

   Figure 4-18. This lookup includes the abbreviations for all the American states. This list is unlikely to change in the near future, so it’s safe to hardcode this rather than store it in another table.

7. Click Next.

   The final step of the Lookup wizard appears.

8. Choose whether you want the lookup column to store multiple values.

   If you allow multiple values, then the lookup list displays a checkbox next to each item. You can select several values for a single record by choosing more than one item.

   Warning

   Once you configure a field to allow multiple values and you save your table, you can’t back out. Access won’t let you modify the field’s Allow Multiple Values setting to convert it back to a single-value field.

   In the State field, it doesn’t make sense to allow multiple values—after all, a person can physically inhabit only one state (discounting the effects of quantum teleportation). However, you can probably think of examples where multiple selection does make sense. For example, in the Products table used by International Cinnamon, a multiple-value lookup would let you create an order for more than one product. (You’ll learn more about multiple-value selections and table relationships in Chapter 5.)

9. Click Finish.

   Switch to Datasheet view (right-click the tab title, and then choose Datasheet View), and then save the table changes. Figure 4-19 shows the lookup in action.

Figure 4-19. When you move to a field that has a lookup, you’ll see a down-pointing arrow on the right side. Click this arrow, and a drop-down list appears with all your possibilities. Choose one to insert it into the field.

Up To Speed: Creating a Lookup That Uses Another Table

In the previous example, you created a lookup list that’s stored as part of your field settings. This is a good approach, but it’s not the best solution. A much more flexible approach is to store the lookup list in a separate table.

There are several reasons to use a separate table:

• It lets you add, edit, and remove items, all by simply editing the lookup table. Even if you think you have a set of fixed, unchanging values, it’s a good idea to consider a separate table. For example, the set of state abbreviations in the previous section seems unlikely to change—but what if the dating service goes international, and you need to add Canadian provinces to the list?

• It lets you reuse the same lookup list in several different fields (either in the same table, or in different tables). That beats endless copy-and-paste operations.

• It lets you store extra information. For example, maybe you want to keep track of the state abbreviation (for mailing purposes) but show the full state name (to make data entry easier). You’ll learn how to perform this trick on Lookups with Related Tables.

Table-based lookups are a little trickier, however, because they involve a table relationship: a link that binds two tables together and (optionally) enforces new restrictions. Chapter 5 is all about relationships, which are a key ingredient in any practical database.

Adding New Values to Your Lookup List

When you create a lookup that uses fixed values, the lookup list provides a list of suggestions. You can choose to ignore the lookup list and type in a completely different value (like a state prefix of ZI), even if it isn’t on the list. This design lets you use the lookup list as a timesaving convenience without limiting your flexibility.

In many cases, you don’t want this behavior. In the Bachelors table, you probably want to prevent people from entering something different in the State field. In this case, you want the lookup to be an error-checking and validation tool that actually stops entries that don’t belong.

Fortunately, even though this option is mysteriously absent in the Lookup wizard, it’s easy enough to add after the fact. Here’s what you need to do:

1. In Design view, go to the field that has the lookup.

2. In the Field Properties section, click the Lookup tab.

   The Lookup tab provides options for fine-tuning your lookup, most of which you can configure more easily in the Lookup wizard. In the Row Source box, for example, you can edit the list of values you supplied. (Each value’s on the same line, in quotation marks, separated from the next value with a semicolon.)

3. Set the “Limit to List” property to Yes.

   This action prevents you from entering values that aren’t in the list.

4. Optionally, set Allow Value List Edits to Yes.

   This action lets people modify the list of values at any time. This way, if something’s missing from the lookup list, you can add it on the fly (Figure 4-20).

Figure 4-20. If you set Allow Value List Edits to Yes, an icon appears under the lookup list when you use it (left). Click this icon to open an Edit List Items dialog box (right) where you can edit the items in the lookup list and change the default value.