Part 6 of "Keeping Your Information Accurate in Access 2003 and Access 2002": Using a lookup list to restrict data (311173)

MORE INFORMATION

It is interesting how many different ways people can come up with to enter the same items of information in a database. Asked to enter the name of their home state, for example, residents of the state of Washington will type Washington, Wash, or WA, plus various typos and misspellings. If you ask a dozen sales clerks to enter the name of a specific product, customer, and shipper in an invoice, the probability that all of them will type the same thing is not very high. In cases like this, where the number of correct choices is limited (to actual product name, actual customer, and actual shipper), providing the option to choose the correct answer from a list will improve your database's consistency.

Minor inconsistencies in the way data is entered might not be really important to someone who later reads the information and makes decisions. Most people know that Arizona and AZ refer to the same state. But a computer is very literal, and if you tell it to create a list so that you can send catalogs to everyone living in AZ, the computer won't include anyone whose state is listed in the database as Arizona.

You can limit the options for entering information in a database in several ways:

• For only two options, you can use a Boolean field represented by a check box. A check in the box indicates one choice, and no check indicates the other choice.
• For several mutually exclusive options on a form, you can use option buttons to gather the required information.
• For more than a few options, a combo box is a good way to go. When you click the down arrow at the end of a combo box, a list of choices is displayed. Depending on the properties associated with the combo box, if you don't see the option you want, you might be able to type something else, adding your entry to the list of possible options displayed in the future.
• For a short list of choices that won't change often, you can have the combo box look up the options in a list that you provide. Although you can create a lookup list by hand, it is a lot easier to use the Lookup Wizard to do it.

In this exercise, you will use the Lookup Wizard to create a list of months from which the user can choose. You might use something like this to gather credit card information. Follow these steps:

1. Open the Field Test database that you created in Part 2 of this series of articles. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

   311168 Part 2 of Keeping your information accurate in Access 2003 and Access 2002: Using the data type to restrict data

2. Open the Field Property Test table in Design view.
3. Add a new field below LastName. Name it Month, and set the data type to Lookup Wizard.
   
   The first page of the Lookup Wizard is displayed:
   
   
   
   You can use this wizard to create a combo box that provides the entry for a text field. The combo box list can come from a table or query, or you can type the list in the wizard.
   
   TIP: If a field has a lot of potential entries, or if they will change often, you can link them to a table. (You might have to create a table expressly for this purpose.) If the field has only a few items and they won't change, typing the list in the wizard is easier.
4. Click I will type in the values that I want, and then click Next.
   
   A combo box typically has only one column, but it can have more. On this page, you can set the number of columns and then enter the text that should appear in each one. If you specify more than one column, you also have to specify which column's text should be entered in the field when a selection is made from the list.
5. Leave the number of columns set to 1, and click in the Col1 box.
6. Enter the 12 months of the year, pressing TAB to create new rows as you need them. Then click Next.
7. Accept the Month default label, and click Finish.
8. Click the Lookup tab in the Field Properties section to view the Lookup information for the Month field, which looks like this:
   
   
   
   The wizard entered this information, but you could easily figure out what you would have to enter to create a lookup list by hand.
9. Click the View button to change to Datasheet view, saving your changes.
10. Adjust the column widths so that you can see all the fields, by dragging the vertical bars between columns in the header.
11. Click in the Month field of a record, and then click the down arrow to display the list, which looks like this:
    
    
12. Click February to enter it in the field.
13. Click in the next Month field, type Jan, and press ENTER. As soon as you type the J, the combo box displays January. If you had typed Ju, the combo box would have jumped to June.
14. In the next Month field, type jly, and press ENTER.
    
    The entry is accepted just as you typed it. Although there might be times when you want to allow the entry of information other than the items on the list, this isn't one of those times.
15. Return to Design view.
    
    The last property on the Lookup tab is Limit To List. It is currently set to No, which allows people to enter information that isn't on the list.
16. Change Limit To List to Yes.
17. Save the table, return to Datasheet view, type jly in a new Month field, and press ENTER.
    
    Access informs you that the text you entered is not on the list, and refuses the entry.
18. Click OK, press ESC to close the list, remove your entry, and then return to Design view.
    
    A list of the names of months 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. There is a solution that will work for both humans and machines.
19. Create a new field named Month2, and again set the data type to Lookup Wizard.
20. Click I will type in the values that I want, and click Next.
21. Type 2 to add a second column, and then click in the Col1 box.
22. Enter the following numbers and months in the two columns, pressing TAB to move from column to column:

       Number   Month       Number    Month
       ------------------------------------
       1        January     7         July
       2        February    8         August
       3        March       9         September 
       4        April       10        October 
       5        May         11        November
       6        June        12        December

    The wizard now looks as shown here.
    
    
23. Click Next to move to the next page.
24. Accept the default selection of Col1 as the column whose data you want to enter when a selection is made from the list, and click Finish.
    
    You return to the table, with the Field Properties section displaying the Lookup information, like this:
    
    
    
    The wizard has inserted your column information into the Row Source box and set the other properties according to your specifications.
25. Change Limit To List to Yes.
26. Save your changes, switch to Datasheet view, and then click the down arrow in a Month2 field to display this list:
    
    
27. 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.
28. Switch back to Design view, and in the Column Widths box, change the width for the first column to 0" to prevent it from being displayed.
29. Save your changes, return to Datasheet view, and as a test, 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.
30. Right-click in the Month2 column, click Filter For on the shortcut menu, type 2 in the box, and press ENTER.
    
    Only the two records with February in the Month2 field are now displayed.
31. Click the Remove Filter button, and then repeat the previous step, this time typing 3 in the box to display the one record with March in the Month2 field.
32. Close the Field Test database, clicking Yes when prompted to save your changes.
33. Close the database.

ADDITIONAL RESOURCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base: