Blocking Bad Data

Even the best database designer has spent a sleepless night worrying about the errors that could be lurking in a database. Bad data’s a notorious problem—it enters the database, lies dormant for months, and appears only when you discover you’ve mailed an invoice to customer “Blank Blank” or sold a bag of peanuts for –$4.99.

The best way to prevent these types of problems is to stop bad data from making it into your database in the first place. In other words, you need to set up validation rules that reject suspicious values as soon as someone types them in. Once bad data’s entered your database, it’s harder to spot than a blueberry in a swimming pool.

This chapter covers the essential set of Access data validation tools:

1. The basics include duplicates, required fields, and default values.
   
2. Input masks format ordinary text into patterns, like postal codes and phone numbers.
   
3. Validation rules lay down strict laws for unruly fields.
   
4. Lookups limit values to a list of preset choices.

All of Access’s data validation features work via the Design view you learned about in Chapter 2. To put them in place, you choose a field and then tweak its properties. The only trick’s knowing what properties are most useful. You’ve already seen some in Chapter 2, but the following sections fill in a few more details.

Every record needs a bare minimum of information to make sense. However, without your help, Access can’t distinguish between critical information and optional details. For that reason, every field in a new table is optional, except for the primary-key field (which is usually the ID value). Try this out with the Dolls table from Chapter 1; you’ll quickly discover that you can add records that have virtually no information in them.

You can easily remedy this problem. Just select the field that you want to make mandatory in Design view, and then set the Required field property to Yes (Figure 4-1).

Figure 4-1.
The Required field property tells Access not to allow empty values (called nulls in tech-speak).

Access checks the Required field property whenever you add a new record or modify a field in an existing record. However, if your table already contains data, there’s no guarantee that it follows the rules.

Imagine you fill the Dolls table with a few bobbleheads before you decide that every record requires a value for the Character field. You switch to Design view, choose the Character field, and then flip the Required field property to Yes. When you save the table (by switching back to Datasheet view or closing the table), Access gives you the option of verifying the bobblehead records that are already in the table (Figure 4-2). If you choose to perform the test and Access finds the problem, it gives you the option of reversing your changes (Figure 4-3).

Figure 4-2.
It’s a good idea to test the data in your table to make sure it meets the new requirements you put into place. Otherwise, invalid data could still remain. Don’t let the message scare you—unless you have tens of thousands of records, this check doesn’t take long.

Figure 4-3. 
If Access finds an empty value, then it stops the search and asks you what to do about it. You can keep your changes (even though they conflict with at least one record)—after all, at least new records won’t suffer from the same problem. Your other option is to reset your field to its more lenient previous self. Either way, you can track down the missing data by performing a sort on the field in question (page 94), which brings empty values to the top. 

WORD TO THE WISE

Don't Require Too Much

You’ll need to think very carefully about what set of values you need, at a minimum, to create a record.

For example, a company selling Elvis costumes might not want to accept a new outfit into their Products table unless they have every detail in place. The Required field property’s a great help here, because it prevents half-baked products from showing up in the catalogue.

On the other hand, the same strictness is out of place in the same company’s Customers table. The sales staff needs the flexibility to add a new prospect with only partial information. A potential customer may phone and leave only a mailing address (with no billing address, phone number, email information, and so on). Even though you don’t have all the information about this customer, you’ll still need to place that customer in the Customers table so that he or she can receive the monthly newsletter.

As a general rule, make a field optional if the information for it isn’t necessary or might not be available at the time the record is entered.

Blank values and empty text

Access supports this Required property for every data type. However, with some data types you might want to add extra checks. That’s because the Required property prevents only blank fields—fields that don’t have any information in them at all. However, Access makes a slightly bizarre distinction between blank values and something called empty text.

A blank (null) value indicates that no information was supplied. Empty text indicates that a field value was supplied, but it just happens to be empty. Confused yet? The distinction exists because databases like Access need to recognize when information’s missing. A blank value could indicate an oversight—someone might just have forgotten to enter the value. On the other hand, empty text indicates a conscious decision to leave that information out.

The good news is that if you find this whole distinction confusing, then you can prevent both blank values and empty text. Just set Required to Yes to stop the blank values, and set Allow Zero Length to No to prevent empty text.

Note:  A similar distinction exists for numeric data types. Even if you set Required to Yes, you can still supply a number of 0. If you want to prevent that action, then you’ll need to use the validation rules described later in this chapter (page 131).

Setting Default Values

So far, the fields in your tables are either filled in explicitly by the person who adds the record or left blank. But there’s another option—you can supply a default value. Now, if someone inserts a record and leaves the field blank, Access applies the default value instead.

You set a default value using the Default Value field property. For a numeric AddedCost field, you could set this to be the number 0. For a text Country field, you could use the text “U.S.A.” as a default value. (All text values must be wrapped in quotations marks when you use them for a default value.)

Access shows all your default values in the new-row slot at the bottom of the datasheet (Figure 4-4). It also automatically inserts default values into any hidden columns (page 91).

Access inserts the default value when you create a new record. (You’re then free to change that value.) You can also switch a field back to its default value using the Ctrl+Alt+Space shortcut while you’re editing it.

Figure 4-4.
This dating service uses four default values: a default height (5.9), a default city (New York), a default state (also New York), and a default country (U.S.A.). This
system makes sense, because most of their new entries have this information. On the other hand, there’s no
point in supplying a default value for the name fields

Tip: One nice feature is that you can use the default value as a starting point for a new record. For example, when you create a new record in the datasheet, you can edit the default value, rather than replacing it with a completely new value.

You can also create more intelligent dynamic default values. Access evaluates dynamic default values whenever you insert a new record, which means that the default value can vary based on other information. Dynamic default values use expressions (specialized database formulas) that can perform calculations or retrieve other details. One useful expression, Date( ), grabs the current date that’s set on your computer. If you use Date( ) as the default value for a date field (as shown in Figure 4-5), then Access automatically inserts the current date whenever you add a new record.

Preventing Duplicate Values with Indexes

Any table’s first rule is that each record it contains must be unique. To enforce this restriction, you need to choose a primary key (page 77), which is one or more fields that won’t ever be duplicated in different records.

Here’s the catch. As you learned in Chapter 2, the safest option’s to create an ID field for the primary key. So far, all the tables you’ve seen have included this detail. But what if you need to make sure other fields are unique? Imagine you create an Employees table. You follow good database design principles and identify every record with an automatically generated ID number. However, you also want to make sure that no two employees have the same Social Security number (SSN) to prevent possible errors—like accidentally entering the same employee twice.

Figure 4-5.
If you use the Date( ) function as the default value for the DateAcquired field in the bobblehead table, then every time you add a new bobblehead record, Access fills in the current date. You decide whether you want to keep that date or replace it with a different value.

Note: For a quick refresher about why ID fields are such a good idea, refer to page 78. In the Employees table, you certainly could choose to make the SSN the primary key, but it’s not the ideal situation when you start linking tables together (Chapter 5), and it causes problems if you need to change the SSN later on (in the case of an error), or if you enter employee information before you’ve received the SSN.

You can force a field to require unique values with an index. A database index is analogous to the index in a book—it’s a list of values (from a field) with a cross-reference that points to the corresponding section (the full record). If you index the SocialSecurityNumber field, Access creates a list like this and stores it behind the scenes in your database file:

Using this list, Access can quickly determine whether a new record duplicates an existing SSN. If it does, then Access doesn’t let you insert it.

So how do you apply an index to a field? The trick’s the Indexed field property, which is available for every data type except Attachment and OLE Object. When you add a field, the Indexed property’s set to No, which means Access doesn’t

UP TO SPEED

How Indexes Work

It’s important that the list of SSNs is sorted. Sorting means the number 001-01-3455 always occurs before 002-40-3200 in the index, regardless of where the record’s physically stored in the database. This sorting’s important, because it lets Access quickly check for duplicates. If you enter the number 001-02-4300, then Access needs to read only the first part of the list. Once it finds the next “larger” SSN (one that falls later in the sort, like 001-02-501), it knows the remainder of the index doesn’t contain a duplicate.

In practice, all databases use many more optimizations to make this process blazingly fast. But there’s one key principle—without an index, Access would need to check the entire table. Tables aren’t stored in sorted order, so there’s no way Access can be sure a given SSN isn’t in there unless it checks every record.

create a field. To add an index and prevent duplicates, you can change the Indexed property in Design view to Yes [No Duplicates]. The third option, Yes [Duplicates OK], creates an index but lets more than one record have the same value. This option doesn’t help you catch repeated records, but you can use it to speed up searches (see the box, “How Indexes Speed Up Searches” on page 195 for more).

Note: As you know from Chapter 2 (page 78), primary keys also disallow duplicates, using the same technique. When you define a primary key, Access automatically creates an index on that field.

When you close Design view after changing the Indexed field property, Access prompts you to save your changes. At this point, it creates any new indexes it needs. You can’t create a no-duplicates index if you already have duplicate information in your table. In this situation, Access gives you an error message when you close the Design window and it attempts to add the index.

FREQUENTLY ASKED QUESTION

Indexes and Performance

Are indexes a tool for preventing bad data or a technique for boosting performance?

Indexes aren’t just for preventing duplicate values. They also shine when you need to boost the speed of common searches. Access can use the index to look up the record it wants, much like you can use the index at the back of this book to find a specific topic.

If you perform a search that scours the Employees table looking for the person with a specific SSN, then Access can use the index. That way, it locates the matching entry much quicker, and simply follows the pointer to the full record.

For more information about how indexes can speed up searches, refer to page 195. However, it’s important to realize that indexes enhance performance only for extremely large, complex tables. If you’re storing a few hundred records, each of which has a handful of fields, you really don’t need an index—Access already performs searches with blinding speed.

Please check back next week for the continuation of this article.