Field validation rules always apply to a single field. However, database designers often need a way to compare the values in different fields. Suppose you have an Orders table that logs purchases from your monogrammed sock store. In your Orders table, you use two date fields: DateOrdered and DateShipped. To keep everything kosher, you need a validation rule that makes sure DateOrdered falls before DateShipped. After all, how can you ship a product out before someone orders it?
Because this validation rule involves two fields, the only way to put it in place is to create a validation rule for the whole table. Table validation rules can use all the SQL tricks you’ve learned about so far, and they can pull the values out of any field in the current record.
A box with extra settings appears on the right side of the window (Figure 4-16).
Note:You can create only a single validation rule for a table. This limit might sound like a problem, but you can get around it by using the And keyword (page 137) to yoke together as many conditions as you want. The validation rule may be a little difficult to read, but it still works without a hitch. Figure 4-16. The Property Sheet shows some information about the entire table, including the sorting (page 94) and filtering settings (page 97) you’ve applied to the datasheet, and the table validation rule. Here, the validation rule prevents orders from being shipped before they’re ordered.
In the Property Sheet tab, set the Validation Rule.
A table validation rule can use all the same keywords you learned about earlier. However, table validation rules usually compare two or more fields. The validation rule [DateOrdered] < [DateShipped] ensures that the value for the DateOrdered field is older than that used for the DateShipped.
When referring to a field in a table validation rule, you need to include square brackets around your field names. That way, Access can tell the difference between fields and functions (like the Date( ) function you learned about on page 119).
Set the Validation Text.
This message is the error message that’s shown if the validation fails. It works the same as the validation text for a field rule.
Lookups
When you insert a new record, Access checks the field validation rules first. If your data passes the test (and has the right data types), then Access checks the table validation rule.
Tip: Once you set the table validation rule, you might want to close the Property Sheet to get more room in your design window. To do so, choose Table Tools | Design -> Show/Hide ->Property Sheet.
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.
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.
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’s mysteriously absent in the Lookup wizard, it’s easy enough to add after the fact. Here’s what you need to do:
In Design view, go to the field that has the lookup.
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.)
Set the Limit to List property to Yes.
This action prevents you from entering values that aren’t in the list.
Optionally, set 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 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.
/ 7 
Getting to Know Microsoft Access 2003, Part 4
