Add or change a lookup column
Sometimes, the value you want is tracked in another table. For instance, suppose you want to record the customer for an order in the Orders table. However, all customer information is tracked in the Customers table. You can create a lookup column that displays the customer information in a combo box or list box control. Then, when you select the customer in that control, an appropriate value — such as the customer's primary key value — is stored in the order record.
This article explains what a lookup column is and how to create one.
If you want to know how to programmatically look up a value in a table, see the reference article DLookup Function. For information about how to create a multivalued lookup column, see the article Add or change a lookup column that lets you store multiple values.
In this article
What is a lookup column?
A lookup column (or field) is a field in a table whose value is retrieved from another table or from a value list. You can use a lookup column to display a list of choices in a combo box or list box. The choices can come from a table or query, or they can be values that you supply. You can create a lookup column manually by setting a field's Lookup field properties, or automatically by completing the Lookup Wizard. Whenever possible, you should use the Lookup Wizard to create a lookup column. The Lookup Wizard simplifies the process and automatically populates the appropriate field properties and creates the appropriate table relationships.
You can create a lookup column in either Datasheet View or in Design View.
Create a lookup column in Datasheet View
By default, when you open a table, it opens in Datasheet view. You can add a lookup column by clicking Lookup Column in the Fields & Columns group on the Datasheet tab. Doing so starts the Lookup Wizard, which leads you through the process of creating the lookup column.
-
Click the Microsoft Office Button
, and then click Open. -
In the Open dialog box, select and open the database.
-
In the Navigation Pane, double-click the table in which you want to create the lookup column — this opens the table in Datasheet view.
-
On the Datasheet tab, in the Fields & Columns group, click Lookup Column.
The Lookup Wizard starts.
-
On the first page of the Lookup Wizard, indicate whether you want to base the lookup column on the values in a table or a query, or on a list of values that you type.
The most common type of lookup column is one that displays values looked up from a related table or query.
-
Click Next, and then follow the instructions to complete the wizard. For more information about completing the wizard, see the section Using the Lookup Wizard.
When you click Finish, a lookup column is created whose field properties are set based on the choices you made in the Lookup Wizard.
Create a lookup column by using the Field List pane
If you are using a database that contains more than one table, you can start the process of creating a lookup field by dragging a field from the Field List pane. The Field List pane displays fields that are available in other tables in your database. When you create a lookup column this way, the Lookup Wizard is automatically started and the field that you drag to the datasheet becomes one of the fields in your lookup column.
-
Click the Microsoft Office Button
, and then click Open. -
In the Open dialog box, select and open the database.
-
In the Navigation Pane, double-click the table in which you want to create the lookup column — this opens the table in Datasheet view.
-
On the Datasheet tab, in the Fields & Columns group, click Add Existing Fields.
The Field List pane appears.
-
Click the plus sign (+) next to a table to display the list of fields in that table.
-
Drag the field that you want from the Field List pane to the table in Datasheet view.
-
When the insertion line appears, drop the field in position.
The Lookup Wizard starts.
-
Follow the instructions to complete the wizard. For more information about completing the wizard, see the section Using the Lookup Wizard.
When you have completed the wizard, the lookup column appears in the table in Datasheet view.
Create a lookup column in Design View
You can create a lookup column in Design view, in addition to Datasheet view. When you work in Design view, you create a lookup column by using the same Lookup Wizard that you use in Datasheet view. However, in Design view, you also have full access to all of the field properties — as a result, you can edit them directly.
-
Click the Microsoft Office Button
, and then click Open. -
In the Open dialog box, select and open the database.
-
In the Navigation Pane, right-click the table in which you want to add the lookup column, and then click Design View on the shortcut menu.
-
Locate the first available empty row in the table design grid.
-
In the first available empty row, click a cell in the Field Name column, and then type a field name for the lookup column.
-
Next, click a cell in the Data Type column for that row, click the drop-down arrow, and then select Lookup Wizard.
The Lookup Wizard starts.
-
On the first page of the Lookup Wizard, indicate whether you want to base the lookup column on the values in a table or a query, or on a list of values that you type.
The most common type of lookup column is one that displays values looked up from a related table or query.
-
Click Next, and then follow the instructions to complete the wizard. For more information about completing the wizard, see the section Using the Lookup Wizard.
When you click Finish, a lookup column is created whose field properties are set based on the choices you made in the Lookup Wizard. You can view the field properties in the bottom pane of Design view under Field Properties. To see the properties that apply specifically to the lookup column, click the Lookup tab.
Understanding the bound value and the display value in a lookup column
The purpose of using a lookup column is to replace the display of a number ,such as an ID (or other foreign key value), with something more meaningful, such as a name. For example, instead of displaying a contact ID number, Access can display a contact name. The contact ID number is the bound value. It is automatically looked up in a source table or query to find the contact name. The contact name is the display value.
1. Display value
2. Bound value
A lookup column has a display value that appears in the user interface, and a bound value that is stored in the control. The display value is "looked up," based on the bound value. This means that Access often displays a looked-up display value that is not the same as the bound value that is stored in the field. For example, in the example of an Orders table, a customer ID value from the Customers table is stored in the Customer field in the Orders table — this is the bound value. However, because the Customer field is a lookup field, Access displays the looked-up value — in this case, the customer name. The customer name is the display value.
It is important to understand the distinction between a lookup field's display value and its bound value. The display value is automatically shown in Datasheet view, by default. However, the bound value is what is stored, what you use in query criteria, and what Access uses by default in joins with other tables.
Introducing the Lookup field properties
You can view the Lookup field properties in the bottom pane of Design view under Field Properties. To see the properties specifically related to the lookup column, click the Lookup tab.
-
Click the Microsoft Office Button
, and then click Open. -
In the Open dialog box, select and open the database.
-
In the Navigation Pane, right-click the table in which you want to add the lookup column, and then click Design View on the shortcut menu.
-
Click a cell in the Field Name column for the lookup column.
-
Under Field Properties, click the Lookup tab.
The Lookup field properties appear.
When you set the first property (Display Control), the list of available properties changes to reflect your choice. You can set the Lookup field properties to change the behavior of a lookup column. Note than when you create the lookup column by using the Lookup Wizard, the Lookup field properties are set for you by the wizard.
Lookup field Properties
| Set this property | To |
| Display Control | Set the control type to Check Box, Text Box, List Box or Combo Box. Combo Box is the most common choice for a lookup column. |
| Row Source Type | Choose whether to fill the lookup column with values from another table or query, or from a list of values that you specify. You can also choose to fill the list with the names of the fields in a table or query. |
| Row Source | Specify the table, query, or list of values that provides the values for the lookup column. When the Row Source Type property is set to Table/Query or Field List, this property should be set to the name of a table or query or to a SQL statement that represents the query. When the Row Source Type property is set to Value List, this property should contain a list of values separated by semicolons. |
| Bound Column | Specify the column in the row source that supplies the value stored by the lookup column. This value can range from 1 to the number of columns in the row source. Note: The column that supplies the value to store does not have to be the same column as the display column. |
| Column Count | Specify the number of columns in the row source that can be displayed in the lookup column. To select which columns to display, you provide a column width in the Column Widths property. |
| Column Heads | Specify whether to display column headings. |
| Column Widths | Enter the column width for each column. If you don't want to display a column, such as an ID number, specify 0 for the width. |
| List Rows | Specify the number of rows that appear when you display the lookup column. |
| List Width | Specify the width of the control that appears when you display the lookup column. |
| Limit To List | Choose whether a user can enter a value that isn't found in the list. |
| Allow Multiple Values | Specify whether the lookup column employs a multivalued field and allows multiple values to be selected. For more information about multivalued fields, see the article Guide to multivalued fields. |
| Allow Value List Edits | Specify whether you can edit the items in a lookup column that is based on a value list. When this property is set to Yes, and you right-click a lookup field that is based on a single column value list, you will see the Edit List Items menu option. If the lookup field has more than one column, this property is ignored. |
| List Items Edit Form | Specify an existing form to use with which you can edit the list items in a lookup column that is based on a table or query. |
| Show Only Row Source Values | Show only values that match the current row source when Allow Multiples Values is set to Yes. |
The bound value in a lookup column is determined by the Bound Column property. The display value in a lookup column is the column or columns that are represented in the Column Widths property as having a non-zero width.
Using the Lookup Wizard
The Lookup Wizard starts in the following cases: when you create a lookup column in Datasheet view, when you drag a field from the Field List pane to a table that is opened in Datasheet view, and in Design view when you select Lookup Wizard in the Data Type column. The wizard walks you through the steps needed to create a lookup column and automatically sets the appropriate field properties to match your choices. The wizard also creates table relationships and indexes, where needed, to support the lookup column.
When the wizard starts, you must decide whether to base the lookup column on a table or query, or on a list of values that you enter. Most of the time, if your database is properly designed and your information is divided into subject-based tables, you should choose a table or query as the source of data for the lookup column.
Table or query based lookup column
If you chose the I want the lookup column to look up the values in a table or query option, when you click Next, the Lookup Wizard displays a list of tables that are available to provide the values for the lookup column. To see only tables, click Tables. To see only queries, click Queries. To see both tables and queries, click Both. When you have chosen a table or query, click Next.
The Lookup Wizard lists the available fields from your table or query. For each field that you want to include in your lookup column, click the field and then click the greater than button (>) to move it into the Selected Fields list. Note that you should select the fields that you want to be visible in addition to the field that you want to supply the value that is stored when you make a selection in the lookup column. When you are finished, click Next.
The Lookup Wizard displays a page that allows you to specify a field or fields that you can use to sort the lookup column. This sorting is optional. When you are finished, click Next.
You can adjust the width of the columns that will appear in the lookup column. If you reduce the width of a field so that it is no longer visible, it will not appear in the lookup column. For example, you can use this feature to prevent an ID column from being displayed. When you are finished, click Next.
When you select a row in the lookup column, you can store a value from that row in your database or use the value later to perform an action. This page of the wizard, shown in the following figure, allows you to choose the field that supplies that value. You should choose a field that uniquely identifies the row. Usually, the primary key field of the source table makes a good choice. When you are finished, click Next.
On the final page of the Lookup Wizard, you should type a name for your lookup column — this becomes the name of the field in your table.
If you want to allow selection of more than one value when the lookup column appears, and then store the multiple values, select the Allow Multiple Values check box. Note that selecting this check box changes the lookup column to a multivalued field.
For more information about multivalued fields, see the articles Guide to multivalued fields and Add or change a lookup column that lets you store multiple values.
Value list-based lookup column
If you chose the I will type in the values that I want option, when you click Next, the Lookup Wizard displays a grid in which you can type the values for the lookup column.
First, enter the number of columns that you want to include in the lookup column. Then, type your values in the grid. You can adjust the width of the columns that will appear in the lookup column. If you reduce the width of a field so that it is no longer visible, it will not appear in the lookup column. When you are finished, click Next.
When you select a row in the lookup column, you can store a value from that row in your database or use the value later to perform an action. This page in the wizard, shown in the following figure, allows you to choose the field that supplies that value. You should choose a field that uniquely identifies the row. When you are finished, click Next.
On the final page of the Lookup Wizard, you should enter a name for your lookup column — this becomes the name of the field in your table. If you want to allow selection of more than one value when the lookup column appears, and then store the multiple values, select the Allow Multiple Values check box. Note that selecting this check box changes the lookup column to a multivalued field.
For more information about multivalued fields, see the articles Guide to multivalued fields or Add or change a lookup column that lets you store multiple values.
Change a lookup column
To change a lookup column, you can open the table in Design View and then modify the Lookup field properties. For information about the Lookup field properties, see the section Introducing the Lookup Field Properties. If the lookup column is based on a value list and the Allow Value List Edits property is set to Yes, you can edit the list items in Datasheet view or Form view. To edit the list items in Datasheet view or Form view:
-
Click the Microsoft Office Button
, and then click Open. -
In the Open dialog box, select and open the database.
-
In the Navigation pane, double-click the table or form that contains the multivalued field.
The table opens in Datasheet view or the form opens in Form view.
-
Right-click the lookup column, and then click Edit List Items on the shortcut menu.
-
Change the value list and then click OK.
Change a field to a lookup column
To change an existing field to a lookup column, in Design view, open the table that contains the field you want to change, click in the Data Type column of the field, click the drop-down list, and then select Lookup Wizard. Then, follow the instructions in the Lookup Wizard to create the lookup column.
-
Click the Microsoft Office Button
, and then click Open. -
In the Open dialog box, select and open the database.
-
In the Navigation Pane, right-click the table in which you want to add the lookup column, and then click Design View on the shortcut menu.
-
Locate the field that you want to change to a lookup column.
-
Next, click the cell in the Data Type column for that row, click the drop-down arrow, and then select Lookup Wizard....
The Lookup Wizard starts.
-
On the first page of the Lookup Wizard, indicate whether you want to base the lookup column on the values in a table or query, or on a list of values that you enter.
The most common type of lookup column is one that displays values looked up from a related table or query.
-
Click Next, and then follow the instructions to complete the wizard. For more information about completing the wizard, see the section Using the Lookup Wizard.
When you click Finish, a lookup column is created whose field properties are set based on the choices you made in the Lookup Wizard. You can view the field properties in the bottom pane of Design view under Field Properties. To see the properties that apply specifically to the lookup column, click the Lookup tab.
No comments:
Post a Comment