Getting the Most out of Access Part 4: Protecting Data Integrity Through Table Design

Part 3

Data integrity is an important consideration in database design and development. The more accurate your data is, the better the information you’ll be able to extract from it.
Your users, regardless of their skill level, are only human. Occasionally everyone makes mistakes. Fortunately, there are many things you can do to help them out and prevent errors from finding their way into your data.

Data Types and Field Sizes

One of the most basic ways to control the data that is entered in a field is to make sure you have selected the appropriate data type. For example, if the field is to contain a date, you should select date/time as the data type. Likewise, if you are expecting numerical data (quantity ordered, age, weight, etc.) you should make sure the field is a numeric data type rather than a text data type. This will prevent any non-numeric characters from being entered in the field.

Taking this a step farther, you can specify the type of numeric data that can be entered or the format of the date. For example, a field like “Age” would probably contain only whole numbers while a field like “weight” might need to allow decimals. Neither would contain letters or other characters. For both records you would select “Number” as the data type. However, for age, you would specify a field size of “Byte”, “Integer” or “Long Integer” for the “Age” field and “Single” or “Double” for the “Weight” field.

The following chart can help you determine which field size to use for numeric fields:

Field Size can also be used to control text entries. If you have a field that is only going to contain a single character, you can prevent the user from entering more than that by specifying a field size of 1.

Default Value and Required

A default value ensures that information gets entered into a field, even if the user does not type anything in. In some cases, you may even want to use a default value and not allow the user to type in a value of their own. An example of this might be a field that records the date the record was added.

To set a default value, enter it in the “Default Value” field on the General tab. If you want to specify a concrete value such as “0” or “false”, simply type it in. You can also use a formula or a function. For example, to make the default value the current date, type in “date()”.

To keep the user from leaving a field blank, set the “Required” field to true.

Input Masks

Input masks control the format of the data that is entered into a field  and (in some cases) provide a clue to the user of what is expected. They can also reduce the number of keystrokes needed to enter data by eliminating the need to type the punctuation characters (such as the dashes in a social security number).

Input masks are often used on phone number, social security number, and date fields. You specify the input mask by entering a formula in the “Input Mask” field. The syntax of this formula can be rather complicated but fortunately, Access provides a wizard to help you out. Click on the “…” button to start the wizard and select the format you wish to use.

Validation Rules

Validation rules allow you to specify certain criteria that the data must meet. Perhaps the entry needs to be within a certain range or a number must be greater than zero. You can also specify a list of valid entries such as “red, yellow, blue” or “1,2, 3” or “Male, Female, Unspecified” The benefit of this is that you virtually eliminate the chance of entering incorrect data in the field. This in turn will allow for more accurate information to be extracted. A human might be capable of looking at a record and understanding that “MLE” or “msle” was meant to be “Male” but if you want to run a query to determine how many of your customers are men, you’re going to have a problem.

To set a validation rule, enter the valid data or a formula that describes a valid entry in the “Validation Rule” field on the General tab. If you are limiting the data to a list of valid entries, type them in like this:

“Red” or “Green” or “Blue” or “White”

If you want to specify a range of valid data, type in a formula such as “>0” or “between 5 and 10” or “<= date()”. The latter example would prevent a future date from being entered.

If a validation rule is violated, Access will send an error message to the user and prevent them from saving the record. You can specify the message they receive by entering it in the “Validation Text” field.

Lookup Fields

A lookup field allows you to limit the data entered to a predefined list of options. For example, you could require the entry to come from a list of products codes, active employees, or local cities. Data like this might come from one of your other tables. You can also specify a list of valid entries like you would with a validation rule (you can’t use a formula, however). The lookup field will then display a dropdown list of all the valid entries.

Lookup fields can be configured manually. To do this, you would select the field and click on the “Lookup” tab. However, for this feature, Access does provide a wizard. To use the lookup wizard, select it as the data type for the field.

 We will cover the Lookup Wizard and lookup fields in detail in our next article.

As you can see, these features are easy to use. They are also powerful tools for maintaining data integrity. By implementing one or more of them, you can go a long way towards improving the quality of your data and the information you can retrieve from your database.