Take the following situation. You have a database and you want users to pick from a list of possible values. Only those values are allowed - the user cannot make their own entry. How do you do this? For example, you have a customer database and you want the user to select the parts a customer wishes to buy.
There are two ways to do this. First, you can set up a field on a form to be a "list" or "combo" box. When you do this, you can type in the values that you want available. The list of possible values is stored within the form so a field called "Parts" could have a list of "Nuts," "Bolts," "Screws," "Nails," and "Rivets."
Great, no problem. Until we want to add another part to the list - say "Tacks." In order to do this, we have to amend the form's structure and add "Tacks" to the list. Not too bad, but you would have to do this in every form that had this list. That could be quite a lot! (What about the supplier order form or the stock form?)
This is starting to get a little messy. Clearly, we need some better method of storing this information. Well databases are great for storing things, why can we get the database to store this information and when we use the combo box, we see the data in the table rather than a hard coded list stored within a form?
We can do this using what are known as "Lookup Tables."
Lookup Tables
A lookup table is standard database table (it has columns and keys just like any other). The difference is, we use it from a form to "lookup" values we have stored elsewhere on the database. This has the following advantages:
· We store data in tables which is easy to modify and report on
· As we are using a table, we don't need to have duplicate lists all over the place stored on different forms.
· When we want to amend the data we are looking up (such as adding "Tacks" to the Parts table), it is simple to do
· One data set used all through the database will mean consistent data throughout the system.
You may sit there and wonder - why not let the user type in what they want rather than messing around with all this?
