Share this Page URL

Chapter 3. Managing the Consistency, For... > Validating Data - Pg. 101

Managing the Consistency, Format, and Integrity of Your Data 101 Some uses of a field validation rule could reduce flexibility that you need as a database changes over time. For example, the requirements for the HelloWorld database listed in Chapter 2 indicate that expenses need to be tracked in predefined categories. The ExpenseType field in the Cam- paignExpenses table records this data. We could use an expression such as the following to create a validation rule for the ExpenseType field that designates the categories in which we track expen- ses: ="Advertising" or "Consulting" or "Production" or "Travel" What would happen if you incurred an expense that didn't fit within one of the categories you named or the finance department added a new category? You could update the validation rule to include a new category, of course, but that would mean testing the data already in the table to be sure that it met the new rule. A better way to define the expense categories we know about at this point and provide for flexibility in the future is to create a table named ExpenseTypes, relate it to the Cam- paignExpenses table, and add a record to the ExpenseTypes table for each expense category. If the categories change, we can add a new record to the table. We can then make the ExpenseType field in the CampaignExpenses table a lookup field that gets its values only from the records in the ExpenseTypes table. (We won't use the Lookup Wizard as we did in Chapter 2 in this exercise. Instead, we'll create the lookup field on our own.) Here are the steps to create the table, which will have only two fields, and the lookup field. When we've finished, we'll check the results. Create a lookup field from scratch 1. 2. In the Database window, click Tables in the Objects list, and then double-click Create Table In Design View. Enter the following information for the table's fields. Field Name ExpenseTypeID ExpenseType Data Type AutoNumber Text Description Unique identifier for the type of expense The type of expense 3. 4. 5. Select the ExpenseTypeID field, and then click the Primary Key button on the toolbar. Click Save on the toolbar, and then enter ExpenseTypes as the name of the table. On the View menu, click Datasheet View, and then enter four records in the ExpenseType field for Advertising, Consulting, Production, and Travel, as shown here: