Table Properties

Besides field properties, there are table properties. The table property sheet is available in Table Design view by selecting View, Properties.

Perhaps the two most significant table properties are the Validation Rule/Validation Text combination. Just as you used a validation rule to limit the range of values in a field, you can use a table property sheet for a rule that applies to two or more fields.

An Orders table presents a typical example. Suppose you have two fields for the number of units in stock, one for the head office (HeadUnits) and one for the branch office (BranchUnits). The total of the two fields shouldn't exceed 100 units for any one item. The way to ensure that is to enter a validation rule of [HeadUnits]+[BranchUnits] <=100. I think that expression is self-explanatory, even if your high school algebra is shaky. Just remember that the fields need to be bracketed and that, in remembering the greater than/less than signs, the piranha (or is it the alligator?) opens its mouth wide to eat the bigger amount. You could also add validation text that would appear in the error messagesay, The total of HeadUnits and BranchUnits cannot exceed 100.

Another use of Validation Rule to maintain data integrity is shown in Figure 5.13. Both Validation Rule and Validation Text properties have been set for the Merchandise table to ensure that no order should be shipped before it has been recorded. In other words, the date the item is shipped either must be the same or must come after the order date. The expression is [ShippedDate]>=[OrderDate], with >= used to mean on or after. The Zoom window, accessed by pressing Shift+F2, is open so that the entire validation text is in view.

Figure 5.13. The table properties sheet for the Merchandise table, with Validation Rule and Validation Text entered.