Access 2007 for Starters: 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'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 ...