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.