Lookups and Blocking Bad Data - Creating a Simple Lookup with Fixed Values (Page 3 of 4 ) 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: Open the table in Design view.
If you’re using the DatingService.accdb example, then open the Bachelors table.
Find the field where you want to add the lookup.
In the Bachelors table, it’s the State field.
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.
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).
Choose “I will type in the values that I need”.
Page 162 describes your other choice: drawing the lookup list from another table.
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 page 162.
Click Next.
The final step of the Lookup wizard appears. Lookups
Figure 4-17.
First you choose the source of your lookup:
fixed values or data from another table.
 Figure 4-18.
This lookup includes the abbreviations for all
the American states. This list’s unlikely to
change in the near future, so it’s safe to hardcode
this rather than store it in another table.
Choose whether or not 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 checking more than one item.
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.)
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 downpointing 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 (on page 140), 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 allows you to 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 seem unlikely to change—but what if the dating service goes international, and you need to add Canadian provinces to the list?
It allows you to 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 allows you to 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 page 162.
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.
Next: Adding New Values to Your Lookup List >>
More MS SQL Server Articles
More By O'Reilly Media |  This article is excerpted from chapter four of the book Access 2007: The Missing Manual, written by Matthew MacDonald (O'Reilly, 2006; ISBN: 0596527608). Check it out today at your favorite bookstore. Buy this book now.
|
|
|
