Sometimes, when I'm cruising Access forums for questions I might be able to answer, I get the feeling that Access is simultaneously a really fantastic application-development tool and a really subtle trap for the unaware.
I'm talking about valid, proper, appropriate, NORMALIZED Relational Table Design, of course. Think, for example, of color options or size options for articles of clothing. Shirts come in Small, Medium, Large and Extra Large. Socks come in Blue, Black, Argyle or white. And so on. You've all seen "tables" in Excel worksheets, with one column for each option. In Excel they are the only option. You have to have different columns for each option. And in the case of two options, you might even have combinations like "Small Blue", "Medium Blue", "Large Blue" and "Extra Large Blue", along with all of the other possible combinations. The inevitable result is multiple columns stretching out to the right as far as the eye can scroll.
Unfortunately, those columns make their way into Access tables as fields as well.
But in Access forums, the problem table design is seldom broached in the initial post, although I have seen a few direct questions about it.
The question often presents itself as a problem with designing an interface to handle multiple fields Should you use a check box to select one of those options, or some other interface element that addresses each of those fields somehow?
Sometimes the question asks for help in constraining data when two or more fields are available, only one of which should be filled and the others left blank . If you pick a Large size shirt, you can't also have it be Small. Black socks can't also be White socks.
Sometimes it shows up as a problem getting results out of a query with that kind of inappropriate table design. How do I write a query to get the Shirt Size we sell most often?
There are many variations, but they all boil down to lack of Normalization, and I compare that to trying to go back and putting in a foundation under a house when you've already put up walls and a roof. It's possible, but it's not going to be easy and it all might collapse on you.
If you plan to create an Access Relational Database Application, and if you don't know what I am talking about here, I think you owe it to yourself to invest a few hours in your peace of mind by learning as much as you can about the Rules of Normalization BEFORE you try to put up walls and a a roof. The nicest roof in the world won't keep the rain out if the building collapses because it lacks a foundation.
The reason this is a problem is that, as I said at the beginning, Access is really open and easy to get started. It's tempting to import a working spreadsheet and throw an interface on. And, unfortunately, that can be done without understanding what the consequences will be.
Be safe, stay dry. Normalize first.
