Ms Access 2010 tutorial: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

Microsoft Access 2010 lookup field-entering values 

- Lookup Wizard dialog box appears

Microsoft Access 2010 lookup wizard dialog box 

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 Microsoft Access 2010 lookup wizard option

- Enter F and M in the list

Microsoft Access 2010 enter values 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 Microsoft Access 2010 view icon to see the result.

Microsoft Access 2010 lookup field 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

Microsoft Access 2010 lookup field using lookup tab 

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:

Microsoft Access 2010 lookup field has no values  

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 Microsoft Access 2010 lookup field get values from the table

- Select TblCustomer in the list

Microsoft Access 2010 lookup wizard table list  

- Click Next and select Sex field

Microsoft Access 2010 lookup wizard select field 

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

Microsoft Access 2010 lookup wizard sort field 

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

Microsoft Access 2010 lookup wizard-redundancy data 

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:

Microsoft Access 2010 lookup wizard query design 

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

Microsoft Access 2010 lookup wizard query view 

This query still displays redundancy values that you don't need. However, you can dealt this problem by adding a key word DISTINCT(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

Microsoft Access 2010 lookup wizard sql view icon 

Or

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

Microsoft Access 2010 lookup wizard 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.

Microsoft Access 2010 lookup wizard query 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:

Microsoft Access 2010 lookup wizard-lookup tab 

- Click View to see the result

 

 




Comments




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.