Using the Lookup Wizard

In Chapter 5, you created the three tables in the sample database. You also entered three type records and a few catalog addresses, as shown in Figure 6.1. At this point, you should be ready to enter a few plant records.

Figure 6.1. You created these three tables in Chapter 5.

Let's open the empty Plants table and enter records for a few of the latest season 's new plants, which are listed in Table 6.1.

Table 6.1. Plant Records

Common Name	Latin Name	Catalog	Type
Yarrow	Achillea millefolium	Wildseed Farms	Decorative
Purple Coneflower	Echinacea purpurea	Wildseed Farms	Medicinal
Cosmos	Cosmos bipinnatus	Gurney's	Decorative
Black-eyed Susan	Rudbeckia hirta	Wildseed Farms	Decorative
Rocket Larkspur	Delphinium ajacis	Wildseed Farms	Decorative
German Chamomile	Matricaria recutita	Gurney's	Medicinal
Calendula	Calendula officinalis	Richters	Decorative

Just to refresh your memory, we'll help you enter the first record:

1. Select the first field in the first row and enter Yarrow .

2. Press the right arrow key and enter Achillea millefolium .

3. Skip the next two fields and enter the TypeID value for decorative. This is where data entry becomes a bit challenging. You probably don't have these values memorized, so open the Types table. After viewing the table, you can clearly see that the TypeID value for decorative type is 1 .

4. Return to the Plants table and enter 1 in the TypeID field for yarrow.

Note

In Chapter 4 we advised you not to enter data directly into a table. During the design and development stage, it's common to play with a few records, just so you can test your design. Later, you'll create data entry forms that you'll use when you actually put the application to work.

Repeat steps 14 to enter the record for Purple Coneflower, which we've planted for its medicinal value. Even though you just looked at the Types table, you'll have to return to that table to learn the value for medicinal plants. To do so, find the Types table on the Windows taskbar and browse the records. This time, the appropriate value is 3 , so return to the Plants table and enter 3 in the Purple Coneflower's TypeID field.

This routine could quickly become annoying, not to mention that it's horribly inefficient. If working with related tables is so efficient, why are you working so hard just to enter the few records shown in Figure 6.2?

Figure 6.2. Remembering the appropriate type value for each record is difficult.

One way to solve this problem is to create what's known as a lookup field in the plant table. A lookup field refers to a field that displays one value but stores another. The best part is that a wizard is available that will help you create the lookup field.

As is, the TypeID values are meaningless, and remembering which value to enter is difficult. If your table had hundreds of records, it would be impossible , so let's convert the plant table's TypeID field to a lookup field. To do so, follow these steps:

1. Open the plant table in Design view by selecting it in the Database window and then clicking Design on the Database Window toolbar.

2. Currently, the TypeID data type is Number. Click the right side of the TypeID field's Data Type column to display its drop-down list.

3. Select Lookup Wizard , as shown in Figure 6.3.

   Figure 6.3. Select Lookup Wizard from the Data Type column's drop-down list.

   

4. The wizard's first panel lets you choose between displaying existing values from a table (or query) and entering a list of items. You want to display the descriptive entries from the Types table, so accept I Want the Lookup Column to Look Up the Values in a Table or Query , which is the default option. Then click Next.

5. When you want to display existing values, the wizard displays a list of tables. The values you want to display are in the Types table, so select Table: Types . Notice the View panel at the bottom of the wizard window. If you want to choose values from a query, select the Queries option to update the wizard's list. Or, you can display both tables and queries by clicking the Both option. Click Next to continue.

6. The next panel displays all the fields in the table or query you selected in the previous window. Generally, you should select the field that is the primary key of the lookup table and the field that contains the values you want to display. In the case of our example, click the double arrow button to move both fields to the Selected Fields list, as shown in Figure 6.4. TypeID is the primary key of the table, and Description is the field whose values you want to display to the user . Click Next when you're ready to continue.

   Figure 6.4. Move both fields to the Selected Fields list.

   

7. The next panel allows you to decide which fields should be used to sort the list of data. In this case, the default sort order is fine, so click Next.

8. The panel shown in Figure 6.5 displays the values the lookup field will display. Notice that the Hide Key Column option is checked by default; that means Access won't display the primary key values (refer to Figure 6.2). Instead, the list will display only the descriptive values shown in the current list. If you need to, adjust the width of the column so you can completely see each entry. To continue, click Next.

   Figure 6.5. Adjust the width of the column if necessary.

   

9. Finally, the wizard borrows the field's name for the new lookup field. Accept the wizard's suggested name and click Finish.

10. When prompted to save the table, click Yes . If you click No, the wizard will discard the lookup field properties you just created.

To see the new lookup field, view the table in Datasheet view by clicking View on the Table Design toolbar. You might recall that the original TypeID field contained numeric values (refer to Figure 6.2). Now that field displays descriptive text instead. Specifically, the lookup field automatically displays the appropriate description for any existing records instead of the value it's actually storing.

The new lookup field also lets you easily enter the foreign key values for each record in the plant table. Refer to Table 6.1 to enter the next record, the one for cosmos. When you get to the TypeID field, click the arrow to open the new lookup field's drop-down list, as we've done in Figure 6.6.

Figure 6.6. Open the lookup field's drop-down list to see the data items you can enter.

You can use the list to enter a type value for a new record by simply clicking the value in the list. Select Decorative to finish the record for cosmos and enter the remaining records in Table 6.1. When you're done, your table should resemble the one shown in Figure 6.7. (Close and reopen the table to see your records sorted as shown.)

Figure 6.7. Seven plants now appear in the plant table.

Open the Plants table in Design view so you can examine the lookup field properties. (Click the View button on the Table Datasheet toolbar.) Next, select any field in the TypeID field row and click the Lookup tab in the Field Properties pane.

Notice that the Display Control is a combo box control. You haven't been introduced to controls yet, but a combo box is a complex control with a text box for entering data and a list. You can enter data directly into the text box component, or you can select an item from the control's drop-down list. You'll learn more about creating and using the various controls that Access offers in Chapter 8, "Creating and Using Data Entry Forms," and Chapter 13, "Customizing Forms."

Note

Did you notice that Access sorted the records after you added the lookup field? The sort really has nothing to do with the lookup field; it just appears that way because the sort showed up after you added the lookup field, which is just a coincidence . Access is actually sorting the records by the primary key values. Because the primary key field is a text field (CommonName), Access sorts the records alphabetically by the values of that field.

It might not matter to you right now, but forms and reports inherit a table's lookup fields. That means any forms or reports bound to the Plants table will use a combo boxnot a text box control, which is the default controlto display the TypeID field's contents. This will make more sense to you in Chapter 8.

Deleting a Lookup Field

To delete the lookup field, select Text Box from the Display Control property field's drop-down list, as shown in Figure 6.8. But don't do so right nowyou need to keep the lookup field you just added to the TypeID field. Close the plant table without making any changes to the lookup field.

Figure 6.8. To delete the lookup field, select Text Box from the Display Control property.

Wouldn't it be much easier to just select an item from a drop-down list than type the entire entry yourself? Not only is it easier and more efficient, a drop-down list is a more reliable data-entry solution because it eliminates typos and other human errors. We recommend you limit the choices a user can enter in this manner as often as possible.

While you were entering records into the Plants table in the last section, did you think that it might be nice to also add a lookup table to the CatalogName field? It certainly would be more efficient, and it would also eliminate typos. You probably had to correct at least an entry or two. (Stay tuned because we'll use another method for displaying items in a drop-down list in Chapter 13.)

Note

You don't realize it now, but the wizard created your first relationship. Later in this chapter, you'll get a closer look at that relationship.