How To: Standardize Data in Microsoft Access

So, you’ve created a database in Access. It has been tested and it works. It was rolled out and people are using it. But something is wrong. When you or someone else use a query to analyze something or compile data, it just doesn’t quite pull up the results you want.

Let’s say that you have a table with customers and it contains the basic contact information and the data types have been set up right.

You make a query to see how many of the customers on your list are in the state of Illinois.

But when you run it, you don’t get all of the results. You know there are more customers in Illinois.

When you check the table, the problem is clear.

Some people have entered the customer information with the two letter postal code and others have used the full state names.

One way that could have guided users into using the two letter code instead of the whole name is by limiting the field size to two characters in the design view.

But it can still lead to more mistakes. A user in a rush may accidentally start entering the zip code in the wrong field.

Knowing how to utilize input masks can prevent these kinds of problems. Using the letter “L” (must be capitalized) for each letter character will limit the entry to letters.

However, using the input mask alone AFTER data has been entered may not be enough. It must be combined with limiting the field size. Unfortunately in the case of using it for state names, it may not be enough:

A simple “Find and Replace” on those states will fix the problem.

And for any new entries, the state field will be limited to two characters that must be letters. Numbers will not work, and no partial information will be allowed. It will either have to be kept completely blank or have two letters entered.

Now the query results will be complete and future entries are more likely to be correct.

Common input masks to get you started include:

In conclusion, Input masks are a great way to take data standardization to the next level. When combined with correct data types and field sizes they serve as a powerful tool to keep data correct for analyzation and compiling purposes.