Lookup Tables in Access

When we say that a table is a lookup table for another main table, it means that the lookup table contains values that will be used as the contents of a field located in the main table. Usually the lookup table has at least two fields – Id and Description. The main table stores only the values from the Id field, but since there is a relationship between the lookup and the main table, the value of the associated Description field is also associated with the record in the main table. On the level of the user interface, lookup tables are often implemented as list boxes or combo boxes.

Linking tables

We can demonstrate this concept using tables which contain information about companies and cities. First, we will link the tables on the table level. The link will be implemented as a list box on the user interface level.

Create following tables:

The CityId field links the tables. They are in a 1:n relationship, which means that: a company can be situated only in one city, but a city can have many companies. A record in the Company table has only one record in the table City, but a record in the City table can have more than one record in the Company table.

It is possible to display a list box to select a city instead of a text box in which to enter a CityId into the Company table. Behind the scene Access will operate with the CityId field. An identifier, which corresponds to a selected city, will be stored in the CityId field of the Company table. Another result of such a link is that the user is prevented from entering a value which does not exist in the City table. You can accomplish this feat in following way:

First, you create the City table in the standard way:

Note that the Data Type of the CityId field in this table is set to AutoNumber.

When creating the Company table, you should include the CityId field as well. Usually, we would set the Data Type to Number (Long Integer). That type corresponds to AutoNumber. In this case we will select another one.

Select Lookup Wizard from the Data Type drop-down list.

Since we want to use values from a lookup table, select the first option, then click Next.

Select the City table as the source of values for your lookup column then click Next.

Select the fields that will appear in the lookup (list box).

Click Next. The Lookup Wizard shows your lookup column.

Usually, you do not need to display the key column (CityId). Click Next to continue. Name the lookup column and click Finish.

If you open the table definition, you will notice that the CityId field's properties are set as follows:

It is also interesting to see how a lookup table is affected by linking. Open the City table and notice the '+' boxes along the left border. When you click on a '+' in one record, Access opens the section of the main table with the records associated with the selected lookup record.

Linking forms

Tables in Access can also be linked on the form level. We can do this on top of linked tables, but it isn't necessary. Forms can be linked even when the tables are not linked. In fact, that is what we recommend. It is better to link forms rather than tables.

Create the City and Company tables as you would usually. Now we want to use forms to view and edit the content of these tables. We should have a list box to display cities on the form that displays Company information. Again, users will be prevented from adding a city for which there is not already a record in the City table.

Create a form to show the Company table in following manner:

• In Design mode, put text box controls CompanyId and CompanyName and a combo box to show CityName from City table.

As soon as you create the combo, Access opens the Combo Box Wizard. Specify that the combo box will look up the values from a table. Click Next.

The wizard opens a window where you select a table to provide the values for your combo box. Click Next.

Select the fields that contain the values you want included in your combo box. Click Next.

The wizard displays a window with a preview of the combo box. Usually, you will not display the key column as a part of your combo. You can also adjust the width of the column(s). Click Next.

The wizard prompts you for the field that this column will be bound to. Select CityId and then click Next.

Name the combo box and then click Finish.

Now, on your form you have a combo box.

If you view the Properties of the combo box, you will notice that the wizard has changed the following properties:

When you activate the new form, you see the following:

When you add a new record, it is enough to type the first character of the name of the city (if the record is in table City), or to select CityName in combo box and it will be added.

Linking forms manually

This whole process can be performed manually as well. You need to create a form that shows the Company table and add text boxes for all fields. Then you need to change the text box for displaying CityName field into a list box. Right click the field and select Change Type from the menu. Select List Box. Then view the properties of the CityName field. Change the following parameters:

After everything is set, the combo box displays only CityName, but your program will use CityId behind the scenes.