Creating a Multi-Column Lookup List


Selecting a month from a list of names is convenient for people, but if your computer has to deal with this information in some mathematical way, a list of the numbers associated with each month is easier for it to use.

In this exercise, you will use the Lookup wizard to create a two-column list of months from which the user can choose.

Use the 06_MulticolumnLookup database. This practice file is located in the Chapter12 subfolder under SBS_Office2007.

Open the 06_MulticolumnLookup database. Then display the Field Property Test table in Design view.

1. Add a new field below Month1. Name it Month2, and set the data type to Lookup Wizard.

2. Select the I will type in the values that I want option, and then click Next.

3. Type 2 to add a second column, and then click in the Col1 cell.

Access adds a second column, labeled Col2.

4. Enter the following numbers and months in the two columns:

Open table as spreadsheet

Number

Month

1

January

2

February

3

March

4

April

5

May

6

June

7

July

8

August

9

September

10

October

11

November

12

December

It is not necessary to adjust the width of the columns in the Lookup wizard other than to make them visible within the wizard itself.

image from book

5. Click Next, and then click Finish.

6. In the Field Properties area, click the Lookup tab to view the Lookup information for the Month2 field.

image from book

The wizard has inserted your column information into the Row Source box and set the other properties according to your specifications.

7. Change Limit To List to Yes and Allow Value List Edits to No.

Tip 

When a property has two or more possible values, you can quickly cycle through them by double-clicking the value, rather than clicking the arrow to open the list.

8. Save your changes, switch to Datasheet view, and then click the arrow in a Month2 field to display the list of options.

image from book

9. In the Month2 list, click January.

Access displays the number 1 in the field, which is useful for the computer. However, people might be confused by the two columns and by seeing something other than what they clicked or typed.

10. Switch back to Design view, and in the Column Widths box-which appears as 1”;1”-change the width for the first column to 0 (you don’t have to type the symbol for inches) to prevent it from being displayed.

11. Save your changes, return to Datasheet view, and as a test, in the remaining records set Month2 to February in two records and to March in one record.

Only the name of the month is now displayed in the list, and when you click a month, that name is displayed in the field. However, Access actually stores the associated number from the list’s first column.

12. Right-click any cell in the Month2 column, point to Text Filters, and then click Equals.

13. In the Custom Filter box, type 2, and then press image from book.

Access now displays only the two records with February in the Month2 field.

14. Click the Toggle Filter button, and then repeat Steps 12 and 13, this time typing 3 in the box to display the one record with March in the Month2 field. image from book

Close the 06_MulticolumnLookup database, saving your changes.



2007 Microsoft Office System Step by Step
2007 MicrosoftВ® Office System Step by Step
ISBN: 0735622787
EAN: 2147483647
Year: 2004
Pages: 231

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net