Lookups and Blocking Bad Data - Lookups
(Page 2 of 4 )
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’s 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. In order for your databases to be useful, you need to make sure you store top-notch 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 ready-made 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 following data types don’t support lookups: Memo, Date/Time, Currency, AutoNumber, Yes/No, OLE Object, Hyperlink, and Attachment.
Next: Creating a Simple Lookup with Fixed Values >>
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.
|
|
