Amal Nagm

Create a lookup column in Datasheet View

1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select and open the database.
3. In the Navigation Pane, double-click the table in which you want to create the lookup column — this opens the table in Datasheet view.
4. On the Datasheet tab, in the Fields & Columns group, click Lookup Column.
   The Lookup Wizard starts.
   
   
   
5. 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.
   
6. Click Next, and then follow the instructions to complete the wizard. For more information about completing the wizard.

Create a lookup column by using the Field List pane

1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select and open the database.
3. In the Navigation Pane, double-click the table in which you want to create the lookup column — this opens the table in Datasheet view.
4. On the Datasheet tab, in the Fields & Columns group, click Add Existing Fields.
   The Field List pane appears.
   
5. Click the plus sign (+) next to a table to display the list of fields in that table.
6. Drag the field that you want from the Field List pane to the table in Datasheet view.
7. When the insertion line appears, drop the field in position.
   The Lookup Wizard starts.
   
8. Follow the instructions to complete the wizard. For more information about completing the wizard. When you have completed the wizard, the lookup column appears in the table in Datasheet view.
   

Create a lookup column in Design View

1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select and open the database.
3. 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.
4. Locate the first available empty row in the table design grid.
5. In the first available empty row, click a cell in the Field Name column, and then type a field name for the lookup column.
6. 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.
   
   
   
7. 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.
   
8. Click Next, and then follow the instructions to complete the wizard. For more information about completing the wizard.

Understanding the bound value and the display value in a lookup column

Display value

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.

1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select and open the database.
3. 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.
4. Click a cell in the Field Name column for the lookup column.
5. 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.
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.

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.

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 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:

1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select and open the database.
3. 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.
4. Right-click the lookup column, and then click Edit List Items on the shortcut menu.
5. 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.

1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select and open the database.
3. 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.
4. Locate the field that you want to change to a lookup column.
5. 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.
   
6. 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.
7. Click Next, and then follow the instructions to complete the wizard. For more information about completing the 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.