Lookup or reference Tables
This example explains how to use Lookup tables. These are very
useful if you want to store relational information, and retrieve it easily for
calculations. For example you may use a lookup table to store words of frequency
and their number counterparts.
Lets take for instance a task that has to be carried out on a
regular basis. We want to let the users type in the frequency of the task in
words to make it easy. But we want to be able to calculate on the value input.
We can use case examples or iff statements but it would be much easier to
create a lookup table and let access do the calculation.
First create the lookup table. We want to store how many days
until the next event occurs. Create a table called tbl_lookup_frequency, with
three fields UniqueID (autonumber)words (text) and NumberDays(number)
You could have whatever values you wanted. But you get the general idea. We
give our users a lot of flexibility to enter information the way they want.
In your form you would have a box that they type the frequency in. If
you wanted to store the number instead of the word but display the word to make
it understandable to the user you would use the combobox wizard to create a
lookup box that showed the words but store the unique key.
If you want to calculate in code convert it by using Dlookup("NumberDays","tbl_lookup_frequency","UniqueID
= " & NumberYouWant)
If you want to calculate in a query. Drag the lookup table into the query and
link from the normal table to the lookup table by UniqueID then just pull into
the query the numberdays field. Access does all the calculating for you.
If you did not use multiple values (i.e. you only allow weekly and not every
week or week) you do not need the Unique ID field and you could store the
NumberDays field in your original table instead.
Common use of these sort of tables is frequency, state Names, post codes, zip
codes, aliases and any type of Abbreviation.