applied tips : january 2009

microsoft access : the dos and don'ts of tables

Getting ready to create a new database, or improve an existing one? Let's review some of the general rules that go into database design, particularly with Microsoft Access (any version).

Put Like Records into the Same Table

One of the tenants of database design is to group like records into the same table. Sometimes, we use the spreadsheet-to-table analogy too often and create multiple tables that are basically storing the same types of records.

For example, if we are storing transactions it might be tempting to create two tables: one for Transactions 2008 and a new one for Transactions 2009.

This concept causes many difficulties down the road and focuses us to reconfigure queries and reports, giving us way more work than necessary.

The recommended solution is to put all of the data into the same table, using a field such as TransactionDate to grab only the data we want. (Remember we can easily filter data using queries in any number of ways.)

Table Names

Naming tables is a fairly easy task, but a few rules should be considered:

Renaming a table can be a frustrating experience, especially if your database already has forms, reports, queries, and even more especially if you've created Visual Basic code to enhance your application. Get the name right the first time and avoid future headaches.

Splitting Fields into More Fields

If a single field contains multiple pieces of data, such as the person's first and last name, you should probably split it into two fields before you get a lot of data entered.

The reasoning behind this is that you might need only one portion of the field sometime in the future. If you were creating a merge letter, and wanted to say "Dear David", you would have to have David in its own field. While there are programmatic ways to split fields as an afterthought, it's a tremendous amount of work.

Another example is a mailing address or city/state/zip combination. Even phone numbers with extensions are often split into separate fields to ease data entry and format the data consistently.

Fields Commonly Forgotten

Here's a few contact fields that are often forgotten:

Field Names

Although Access will allow you to name a field almost anything, there are some general conventions to follow:

Following these guidelines will help create a database that scales easily and provides compatibility with the built-in programming language Visual Basic.

Data Types and Restrictive Properties

Especially when starting a new database, be sure to review each of the Data Types and their Properties to force data entry to be as correct as possible.

For example, changing a field from Text to Date/Time requires that the user enter a valid date (in any format).

Properties like "Required", "Input Mask" and "Indexed" can also help you restrict or conform data entry and avoid problems in the future.

Using a Lookup (to create a Pull-Down menu) is another way to add functionality to the database and enforce proper data entry.

Future Proofing

While improving your database, ask yourself what you can do today to avoid future problems. What features might you need in six months or a year that you should start to make available today?

If you think that it's likely you might someday need the email addresses of your customers, even if you're not sending them email today, create the field so that it can be populated when available.

Conclusion

Creating a database is a lot like creating a house, and you can't do that until you've got a sufficient idea of what the house is for. Even then, without blueprints and a firm idea of what you're building, you wouldn't want to start laying concrete or raising walls.

The best databases are those with lots of forethought, without rushing into things.

schedule a class or get consulting on access

Request an on-site Access class from Applied Office or get some helpful consulting. Learn more here