Setting up your Access database for success

I recently shared some guidance on when to use Microsoft Access vs. Excel for storing your data. Regardless of which tool you use, the quality of your data is critically important. So it’s vital that everyone who enters data does it in the same way. If they don’t, you won’t be able to do any meaningful analysis of that data later.

Once you've decided that Access is the best tool for your job (and if you have multiple people entering data, it probably is!), you'll want to know how to maintain control and consistency over your database, right? Over the next couple days, we will look at the tools in Access designed to help your team enter the right information in the right way in the right places.

Let’s start today by talking about how you want users to work with your database. (If you're not sure how to structure your database, this Office Online article can help).

Access provides two totally different ways to add information to a database. There’s the table view, which vaguely resembles an Excel worksheet, and there are forms, which can appear anyway you want. There are advantages to each.

The table view is easy to set up. It’s automatically generated when you create your database. And if the people using your database are already used to working in rows and columns (either in Access or Excel), the table view may be the easiest to use:

In the table view, however, the user has an incredible amount of power. In it, they’re looking at the actual database. All the raw data is right there. The table view lets them look at every record and field. Users can jump to any field and make changes, or easily delete entire records.

Maybe you don’t want your users to have that much power. You might be concerned that temporary staff hired simply to perform data entry could accidentally delete valuable information, or there may be privacy issues. If that’s the case, you will want to create a form.

With forms, you design data entry and modification screens. Users see only the fields you want them to see:

You can also lock records in forms. This permits the database users to make changes to the information in records, but they’re blocked from deleting those records entirely.

The Designer tool in Access lets you do all sorts of snazzy things to your forms, but it’s important to keep your forms simple and consistent. They shouldn’t be so complex that they feel like an entirely different program. Also, the simpler your forms are, the easier it will be to add new forms when you need to extend your database. For help getting started with forms, review this Office Online article.

Tomorrow, we’ll look at how to set up your database to prevent someone from accidentally entering data in the wrong places.

Suzanne