How to Normalize Data in Microsoft Access

The term “normalization” gets thrown about quite a bit in database circles, especially to try to explain to newer users a couple of principles of data organization, but seems rather vague to someone not acquainted with database-ese.

The idea of making data “normal” is not too far from the meaning used by database designers, when one understands what “normal” is. When we create, say, a batch of file cards with names, addresses, etc., we tend to lay out the information similarly—consistently—so as to make it easier to follow on each. If each piece of information is in its own place, a “field” or space designated for each, we’re already close to the idea of normalization. One wouldn’t want to put the first and last name in the same field, since we might want to sort by last name first. We couldn’t do it easily if the two were in the same place. Nor would we want to put street address, city, state, and zip code in the same field, for the same reason.

Bad Addresses

So one rule we try to follow regardless of what kind of database software we use, MS Access for example, is that we should put one piece of information in each field. A rough analogy would be, if one wanted to fill a glass jar’s volume completely, using something like golf balls or hard candies would not work too well, as they’re too big to really follow the shape of the jar. But using gravel, or sand, or better still, flour, would be better. The individual pieces are smaller, and can fit the space much better. The smaller the pieces, the more “flexible” they are in filling the space.

Jars

Secondly, it’s a good idea to divide the tables (or collections of data) into categories—an employee database might contain a table of personal data, a table of office data, one of health plan data, one of travel/transport data, and so on. Turns out it’s easier for most databases—and users—to work with a larger number of smaller tables than the other way round.

Finally, there is at least one exception. Some kinds of data, such as addresses, work better with a little de-normalization. (Access makes this fairly easy to see.) We don’t want to put a zip code in the same field with city and state, but putting city, state, and zip fields in an address table—even though slightly redundant—make the addresses much easier to understand and use.

Address Table

Align Fields in Access Forms

Working with Microsoft Access can be kind of intimidating when you start, especially because there seem to be so many details to absorb and keep track of. And even fairly experienced database people have to watch out for the little things.
One which really drives some new Access users up the wall has to do with forms. Doing form layout is an art unto itself, and getting a good layout can take time. Even after you’ve got it mostly cleaned up, the program has one particular nitpick some people don’t even notice. At first.

Form Design View
When you work on a form in Design view, the most time-consuming thing is sizing and moving fields (officially known as text boxes, where data show up) and labels (to tell you what the fields contain). And making sure the Tab key gets you from field to field in a logical order is important. Luckily, we can use the Tab Order dialog box to do this.
Tab Order Box

At this point, the tab order doesn’t match the order of the fields because we rearranged them. We want to tab left to right, top to bottom. So we arrange our fields, bring up the box, and click Auto Order.

Auto Order Button
BUT!…if any of the fields are not aligned just right, by their top edges, with their buddies in the same row, whoever’s highest gets dibs in the tab order. (“D’oh!”)
So we select the fields and labels in question, go to the Arrange tab->Sizing and Ordering group, and click the Align button. In the dropdown, we click Top to get everything in a given row to line up.

Align Top Command

Problem solved.
Then, go back to the Tab Order box, click Auto Order once more, and test. That should do it.
Yes, it’s kinda nitpicky. But it’s worth it, as getting this sort of thing right can improve data entry speed in Access, and other database programs quite a bit…and win you the approval and accolades of the people who do it.

How to Use VBA in Microsoft Access Tutorial


In this Microsoft Access Tutorial, you’ll see how to begin using VBA in Microsoft Access 2007. The basic concepts of objects, properties methods and events are explained and then you will see how to attach VBA code to the load event of a Microsoft Access Form and to the click event of a Command Button on a form. This tutorial is from our live, instructor-led online course: Microsoft Access 2007 VBA Training course. To learn more, visit our Microsoft Access Courses Page.

Using ADO to Retrieve Data : Microsoft Access Tutorial

access-ado-vba-data
In this Microsoft Access Tutorial you’ll see how to connect to a database using ADO Connection, Command and Recordset objects in VBA code, how to populate text boxes from fields in the data retrieved, and how to create command buttons that use ADO Recordset methods to allow the user to move throughout the data. This content is from our live, instructor-led online MIcrosoft Access 2007 VBA Training course. To learn more, visit our Microsoft Access Courses Page.

MS Access Resources

ms-accessMicrosoft has recently published a very thorough set of MS Access 2010 resources to the TechNet site. Access has always been the part of the Office Suite that straddles the end-user/IT Professional fence. The resources on TechNet are definitely geared toward Access Developers rather than folks that might be using MS Access more casually. (more…)