Tips on How to Keep Your Access 2007 Database Clean with Four Settings

No matter how nicely you have set up your database to collect and retrieve data, the databases format itself will not help you create useful queries and reports if the data within the database is a mess. Remember that you may not be the only person adding, deleting, and modifying data in a database and sometimes you have no control over how a person uses the database.

One way to combat bad, missing, or unusable data is to use Access 2007’s four field settings. Using these settings, you can specify how data is entered and displayed in a table, whether cells may be left empty, and several other important features you want to control for how data may be added to a table. Read on to learn how to use four Access 2007’s settings to gain better control over your data. The result will be a cleaner and more useful database.

Changing the format in an Access 2007 table changes the way data is displayed in your database table but it does not change the data itself or how the data is stored. This is useful for catching errors and typos when entering data into a table.

Suppose that you often type in large numbers into a database table and you want to be sure that correct numbers have been entered. For example, it can be difficult to tell the difference between the following numbers:

10000 and 100000

Notice that the first number is ten thousand and the second number is one hundred thousand. This is a ten-fold difference in scale. By formatting the field in your table, you can force Access 2007 to display separators such as commas for large numbers. Now it is much easier to see the difference between ten thousand and one hundred thousand:

10,000 and 100,000

Although this won’t guarantee that numbers typed into the table are correct, it does reduce the potential for human error. Of course, data in an Access 2007 table can be formatted in many ways without changing the actual data in the table. Play around with this setting to reduce errors.

When typing in data into a database, it is possible to be in the wrong field and end up typing in an address where a phone number needs to go. Remember that some users of your database may not be as neat as you and can just start typing in data wherever they please. The result can be a very untidy database.

Using an input mask, you can specify a rule that will not allow a user to type in data in a place where it doesn’t belong. Input masks are simply a series of characters that tell Access 2007 what data to expect in a certain field.

For example, suppose you have a “phone number” field where you intend to store customer phone numbers. There is no reason why a user would need to type letters into a record in such a field so you can restrict the data in that field to the digits 0 through 9.

Doing this will not allow a user to type in an address where a phone number should go. However, input masks are much more powerful than this. You can use Access 2007’s built in input masks or you can create custom input masks. Play around with this feature and make your database much cleaner.

Up until now, we have discussed how data is displayed in a table (formatting) and what type of data may be placed in a record’s field (input mask). However, we haven’t discussed whether a user must type in data.

One way to keep your database data clean is to disallow the creation of a record unless all of the required fields have some type of data in them. For example, you wouldn’t want a user of your database to create a new customer record unless all of the data for that customer is placed in the record. For example, you don’t want to come back to your database and find that a customer placed an order but you have no address, order number, etc. for that customer.

Access 2007 solves this problem by allowing you to require that certain information is placed in a record before the data can be saved. Otherwise, the record is not created. The required property of a table has two settings: Yes, means that a user cannot create a record unless something is placed in the field in question. No (the default property) means that the field for a record may be left empty. By manipulating the required property of a field, you can better ensure that your database will be complete.

Using the validation property, you can make Access 2007 test new data typed into the database against a rule you have created. Compliance with the rule makes Access 2007 behave normally. Deviation from the rule allows you to display an error message that tells the user that something is wrong and it needs to change.

Validations are usually used for numbers, currency, and dates but can be used for text fields. Unfortunately, things can get messy with text validations because it is usually impossible to predict everything a user might try to enter into a database, some of which may be completely valid data. This one can take some practice to use so be careful before you create a validation without testing the rule extensively.

Data in an Access 2007 database can become a mess very quickly if you do not place some controls on the data being entered into the tables. The four settings discussed in this article can help you keep better control over what comes in and goes out of your database. At the very least, consider formatting the fields so that data is easier to read. The data itself in the table does not change in this case but it can be made to be much more human-readable. This alone can help keep your database clean and error-free.