Access Design and Programming Tips

by Steven Roman, author of Access Database Design & Programming, 3rd Edition

Unlike other applications in the Office Suite, Access requires careful planning and thought before creating something useful. Additionally, even though Word, Office, Outlook, and other applications can be customized using Visual Basic for Applications, Access, in particular, has a gold mine of power that lies beyond the user interface. In this article, I present a few simple tips I have found helpful when designing and programming in Microsoft Access. I hope you will find them useful as well.

Don't touch your PC until you have taken the time to rough out a design of your database.

All but the simplest database applications require some thought about the design of the database itself, that is, the design and content of the tables in the database. (Of course, the forms and reports also require design, but this is a user interface issue, not a database design issue.)

I would strongly urge that you spend some time with pen and paper roughing out this design before even touching the computer. You can waste a lot of time redesigning existing Access tables, not to mention the consequent redesign of dependent queries, forms, and reports. So it is much better to have pretty good idea of your database's table design before starting Access.

To be sure, you will probably spot changes that need to be made after you are well into the development cycle of the project, but that can't be helped. I don't know anyone who can see completely through to the end of a project during the initial stages of development.

Related Reading

Access Database Design & Programming, 3rd Edition Access Database Design & Programming, 3rd Edition
By Steven Roman
Table of Contents
Sample Chapter
Full Description

Keep one thing in mind: an Access table is intended to represent a single entity. For instance, a table may represent book entities, or author entities, or publisher entities--but only one of these entities. A table whose fields are:

Author ID
Author Name
Pub ID
Pub Name

is clearly a table about three entities: books, authors, and publishers. This is bad database design. Keeping this one axiom in mind will save you from numerous problems down the road.

There may be more than one right design for a database.

Recently, I had to import a collection of Excel spreadsheets into Access for a well-known restaurant company. Each sheet represented advertising data for a given market. For instance, the Los Angeles market might have data such as (This data is fictional.):

DMA: Los Angeles
Daypart Mix EE PR LE LN
M1 12% 56% 12% 20%
M2 12% 56% 12% 20%
M3 12% 56% 12% 20%
M4 12% 56% 12% 20%
M5 12% 56% 12% 20%

The rows of the table refer to different advertising campaigns (also called modules). The column headings refer to times of day (that is, dayparts) in which the advertising took place. For example, advertising for module 1 took place as follows: 12 percent early evening (EE), 56 percent prime time (PT), 12 percent late evening (LE) and 20 percent late night (LN). Finally, DMA stands for Designated Market Area.

Two ways present themselves in terms of turning this data into an Access table. One is a horizontal design, using the fields:


The other is a vertical design, using the fields:


In the former case, each row of the spreadsheet becomes a row of the Access table, as in:

DMA=Los Angeles, Module=M1, EE=12%, PR=56%, LE=12%, LN=20%

In the latter case, each row of the spreadsheet becomes four rows of the Access table:

DMA=Los Angeles, Module=M1, Daypart=EE, Value=12%
DMA=Los Angeles, Module=M1, Daypart=PR, Value=56%
DMA=Los Angeles, Module=M1, Daypart=LE, Value=12%
DMA=Los Angeles, Module=M1, Daypart=LN, Value=20%

Which is good database design? They both are. It just depends on what you need to do with the data. In fact, either table may suit your needs.

Comment on this articleDo you have any Access design and programming tips of your own?
Post your comments

The first table is about Module entities. The second table is about daypart entities. If you need to do a lot of calculation involving different dayparts, then the second table may be better suited, but if the calculations involve only one daypart at a time, then the first table may be better, for it is generally easier to compute across rows than across columns.

The real point here is that there may be more than one reasonable database design for a given project, and you may need to do some experimenting before you can select the optimal design for your project.

Be aware of AutoNumber field limitations.

As you probably know, AutoNumber fields can save you work when adding data to a table programmatically. You don't need to find an available key value and confirm that it is free because AutoNumber fields do this for you automatically.

However, there can be a price to pay--one I didn't know about until it was too late. Namely, you cannot have two AutoNumber fields in the same table.

This might not seem like a problem at first, but consider the possibility that you might want to merge two tables, each with an AutoNumber field, into a third table.

If this ever comes up, you won't be able to create a make-table query that includes the AutoNumber fields from each table. Just keep it in mind.

Pages: 1, 2

Next Pagearrow