How to Create a Lookup Field in Microsoft Access 2007

How to Create a Lookup Field in Microsoft Access 2007
Page content

Defining Lookup Fields

One way to improve the design of an Access database is to utilize lookup fields whenever you have information that is repeated in multiple tables. Even if you’re only working with a relatively simple database, you may want to consider using lookup fields for other reasons. For instance, if one of your fields contains the two-letter postal abbreviation for the state in a U.S. mailing address, a lookup table can help prevent keying errors on the part of data entry operators.

Creating such a field actually takes very little time, especially if the information already exists in another table or query. The following steps describe how to perform this task in Access 2007.

Creating a Lookup Field

Step 1: Open the table that contains the field that you want to define as a lookup field. Make sure that you’re viewing the table in Design view. To do this, click on View in the Home tab of the Access ribbon and select Design View. (Click any image for a larger view.)

Step 2: Select Lookup Wizard from the drop down box in the Data Type column next to the field that you want to define as a lookup field.

Select Lookup Wizard

Step 3: When the Lookup Wizard appears, first choose if you plan to look up the values for this field in a table or query or if you plan to manually type in the values you wish to use.

Lookup Wizard Opening Screen

Click Next to continue.

Step 4: From here, we branch off into two paths depending which method is chosen in Step 3 – Table/Query or Manual Entry.

Manual Entry

If you choose to manually enter the information, the next portion of the Lookup Wizard will provide a blank table where you can enter the information.

Manually Enter Lookup Information

Choose the number of columns that the table should contain, and then type or paste the information into the table. When done, click Next to continue and skip ahead to Step 5 below.

Table/Query

If you are obtaining the information for the lookup field from an existing table or query, you’ll be asked to specify that object in the next window.

Specify Table or Query

Select the table or query that contains the information that you want to use to build the lookup field and click Next to continue.

The next window will list all the available fields in that table or query. Choose the fields you want to use and move them to the Selected Fields column.

Pick Fields

Click Next to continue.

The next screen is optional, but here you can decide how the data for the lookup field is sorted. If you have a sorting preference, choose which fields you would like to have the information sorted by and what type of sorting method to use.

Decide Sorting Orders

When finished, click Next.

In this screen, you can choose to adjust the column width. This can be useful if your lookup field data contains elements with long text descriptions.

Adjust Column Width

Click Next.

Step 5: In this next step, you can designate a name for the lookup field. Put a check in the appropriate box if you want to allow multiple values. Otherwise, only unique entries will be shown.

Name the Lookup Field

Click Finish and the lookup field will be generated.

Additional Resources: For more tips and tricks, be sure to browse through the other Microsoft Access user guides available here at Bright Hub.