Add or change a lookup column Access 2007

Author: mety Labels::



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.
Lookup column
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.
  1. Click the Microsoft Office Button Button image, 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. Access Ribbon Image
    The Lookup Wizard starts.
    Lookup Wizard page on which you choose table/query or value list

  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.
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.
  1. Click the Microsoft Office Button Button image, 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. Access Ribbon Image
    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. Insertion line for inserting a field from the Field List pane
    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

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.
  1. Click the Microsoft Office Button Button image, 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.
    Lookup Wizard page on which you choose table/query or value list

  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.
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.
Bound versus display value in a lookup column
Callout 1 Display value
Callout 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.
  1. Click the Microsoft Office Button Button image, 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 propertyTo
Display ControlSet 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 TypeChoose 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 SourceSpecify 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 ColumnSpecify 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 CountSpecify 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 HeadsSpecify whether to display column headings.
Column WidthsEnter 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 RowsSpecify the number of rows that appear when you display the lookup column.
List WidthSpecify the width of the control that appears when you display the lookup column.
Limit To ListChoose whether a user can enter a value that isn't found in the list.
Allow Multiple ValuesSpecify whether the lookup column employs a multivalued field and allows multiple values to be selected.
Allow Value List EditsSpecify 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 FormSpecify 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 ValuesShow 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.
Lookup Wizard page on which you choose table/query or value list

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.
Lookup Wizard page that shows tables and queries to select
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.
Lookup Wizard page on which you select fields
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.
Lookup Wizard page on which you set the sort order
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.
Lookup Wizard page on which you adjust the width of the columns
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.
Lookup Wizard page on which you choose the bound value
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.
Lookup Wizard final page
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.
Lookup Wizard grid for entering values
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.
Lookup Wizard page on which you choose the bound value
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.
Lookup Wizard final page

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 Button image, 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 Button image, 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.
    Lookup Wizard page on which you choose table/query or value list
  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.

0 comments |

Labels

Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2