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
Just to refresh your memory, we'll help you enter the first record:
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:
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."
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.)