Microsoft Access: Understanding Lookup Fields

When you need to design a specific table to perform data validation or to make life easier for data input purposes it is strongly advised that you use Lookup Fields. With a Lookup Table consisting of Lookup Field data items you can use a table with only one or two fields such as a customer or contact ID and name, and then use these Lookup Fields over and over on multiple input screens.

The Microsoft Access Lookup wizard makes this process incredibly straightforward so that you can either

• Create a lookup value list which displays values from an existing table or query.
• Create a fixed set of values which are setup in their own right purely for the purpose of creating subsequent fields.

For example these ‘masterfiles’ of quick-link data could hold information pertaining to personal demographics - sex, gender, religion, ethnicity, etc, or business details such as geographical location, buying categories, budgets, etc. Lookup fields are very powerful not just for slick information retrieval but also help keep a database running at an optimum level of performance because they reduce the duplication of effort and data storage. Lookup tables can be used to drive all underlying information and in conjunction with Primary and Foreign Keys make a database easier to use from an input clerk, administrator and output point of view as their contents can be accessed by all of your tables.

1. Open the table in Design view.

2. If the field you want to use as the foreign key for a lookup field already exists, click in the field’s row.

3. In the Data Type column click the arrow and select Lookup Wizard.

4. Click the option ‘I want the lookup column to look up the values in a table query’.

5. Click next and from the subsequent screen select the table or query containing the values you wish to lookup.

6. Click next and from the subsequent screen choose the fields you wish to use for a lookup list.

7. Click next and you will be asked by the lookup wizard to choose a label. Enter a relevant label, i.e. keeping the same as the field name.

8. Click finish. To see the settings of the lookup click on the Lookup tab in the field properties.

Microsoft Access will now create the lookup field and sets the field properties based on the choices you made within the wizard. Once you've added this lookup list you can amend the entries, add to them, and add the field to any form. Access will perform this action automatically by copying its definition into the form. However be aware that if you do change the definition of a lookup, changes will not be reflected in any subsequent form that the lookup is attached to unless you delete the field from the form and add it again.

1. Open the table in Design view.

2. In the field name column type the name for the field.

3. In the Data Type column click the arrow and select Lookup Wizard.

4. In the first dialog box, click the option that indicates ‘I will type in the values that I want’.

5. Click next and enter the number of columns you want to see followed by the text entries that you want the lookup list to hold.

6. Click next and set the label as you wish, remember it is probably best to set the label name to the same as that of the field name to save on confusion later.

7. Click finish. To see the settings of the lookup list click on the Lookup tab in field properties.

Microsoft Access will now create the fixed value list and set field properties based on the choices you made within the wizard. Once you've added this lookup list you can amend the entries, add to them, and add the field to any form. Access will do this by automatically copying the definitions to the form in question. However be aware that if you do change the definition of a value list field these changes will not be reflected within the form until you delete the field from the form and add it again.

In the following part we will discuss setting up Relationships Between Tables and how powerful relational database interfacing can provide a whole new dimension of database interconnectivity that enhances your entire information flow.

>>> Microsoft Access: Designing a Relational Database (Part 1)

>>> Microsoft Access: Designing a Relational Database (Part 2)

>>> Microsoft Access: Understanding Data Types

>>> Microsoft Access: Understanding Field Properties