09. Lookup Wizard

 

Lookup Wizard

XII. Lookup Wizard

The Lookup Wizard data type allows you to create a lookup field/column, which you can choose a value from another table or from a list of values by using combo box or list box.

XII.1. Create Lookup Field By Entering Values

This option allows you to enter the values that you want to display in the Value List(Value list:  a type of Row Source Type property that you must select if you choose lookup field by entering values. If so the process of lookup field cannot operate.) when click lookup field. For example, you want the Sex field of TblEmployee to display F for female and M for male when you click this field. To do so, follow one among the two ways below:

- Open TblEmployee in Desing View

- Put cursor in Sex field

- Select Lookup Wizard

 

- Lookup Wizard dialog box appears

 

The dialog box asks you to choose the option that you want your lookup field to get values from, entering value by yourself or get from a table or query.

- Click 

- Enter F and M in the list

 

If you want your lookup field to display two columns type 2 in text box in front of Number of columns. See the dialog box above.

- Click Next

- Click Finish

- Click  to see the result.

Or

- Open TblEmployee in design view

- Put cursor in Sex field

- Click Lookup tab, under the Field Properties

- Select Value List in front of Row Source

- Enter "F";"M" in front of Row Source

 

If you want to change or delete the lookup value follow the steps below:

- Open the table in design view

- Click the lookup field

- Click Lookup tab

- Add or delete the values in front of Row Source

For example, you delete "F"; "M" values in front of Row Source in the Sex field of TblEmployee. When you view the table and click the Sex field, you may see the lookup field display like the figure below:

 

The lookup field remains the drop down list. You can delete it by following steps below:

- Open TblEmployee in design view

- Click the Sex field

- Select Text Box in front of Display Control

Note: Only Text and Number data type of field can do lookup wizard.

XII.2. Create Lookup Field By Getting the Values of a Table or Query

In this wizard, allows you to get the values from the table or query. For instance, you want Sex field of TblEmployee to display F and M in the list. These values get from Sex field of TblCustomer. To implement the example, do the following steps:

- Open TblEmployee in design view

- Put cursor in Sex field

- Select Lookup Wizard

- Click 

- Select TblCustomer in the list

  

- Click Next and select Sex field

 

- Click Next and sort Sex field as the figure below:

 

- Click Next and then click Finish

- Click View to see the result

You will see the lookup field display a list data as the figure below:

 

Lookup data in the list show duplicate values because the word "Male" is stored in many rows. If you don't want the list data to show the duplicate values, you have to solve this problem by creating a query as the figure:

 

To create a query:

- Click Create tab --> Query Design  in the queries group--> Select TblCustomer in the list of Show Table dialog box --> Click Add button

- Double-click on Sex field, you created a query as the figure above.

- Click View see the query result

 

This query still displays redundancy values that you don't need. However, you can dealt this problem by adding a key wordDISTINCT(this key word is used to select a unique value) in the SQL code. 

To view the SQL code:

- Click SQL View on the Access status bar

 

Or

- Right-click on the blank query window, click SQL View

 

The SQL code display as the following code:

SELECT TblCustomer.Sex
FROM TblCustomer;

Add the key word DISTINCT in front of SELECT:

SELECT DISTINCT TblCustomer.Sex
FROM TblCustomer;

After you add the key word DISTINCT, click View or Run the result.

 

Now, you can create a qualify lookup field with unique data in Sex field of TblEmployee. Follow all steps above; select Query11 instead of TblCustomer in Select TblCustomer in the list step and click View to see the result.

You can use Lookup tab to create a lookup field that gets the data from the table also. This way is shorter than the first way. But you have to have a query as a Query11 and implement the following steps:

- Open TblEmployee in design view

- Click the Sex field

- Select Combo Box or List Box in front of Display Control

- Type SQL Select Statement in front of Row Source as the figure below:

 

- Click View to see the result