Microsoft Office Tutorials and References
In Depth Information
Creating a Lookup Field
✦ 99:00:00 >LL: This mask, for long time format, allows two digits; displays
a colon; requires two digits; displays a colon; requires two digits; displays a
space; and requires two letters, which are displayed in uppercase.
Creating a Lookup Field
You want your database to be as easy to use as possible, right? You also
want data to be entered consistently. As orders are entered, for example,
you want the name of each product to be entered so that Access can find
it in the Products table where you’ve stored details such as the price.
What’s the chance that the product name, entered as part of an order,
actually matches the exact product name listed in the Products table? Pretty
minimal . . . unless you create a lookup field.
A lookup field provides users a list of choices rather than requiring them
to type a value in the datasheet. You could think of using a lookup field
as adding a field from an existing table to your new table. Access uses the
field from the other table to create a drop-down menu of your products for
users to choose among as orders are entered. Lookup fields enable you to
keep your database compact and the data entered accurate and consistent.
Lookup fields are very useful and not as complicated as they sound.
The items on the drop-down menu can come from a list you type, or they
can be from a field in another table. Storing values for your drop-down menu
in a table gives you much more flexibility if you want to modify the list or
store additional information about the values later. (If your list contains
state abbreviations, for example, you may also decide to include full state
names and even state tax rates.) Storing the drop-down-menu data in a table
enables you to display one field (such as a customer’s full name) and store
another (such as a customer number). Working with the logical
relationships you set up among tables, you can store less data — thus keeping the
database compact — and make entering and manipulating your data easy.
Here’s a hint: In almost all cases, it’s best to keep the values for your lookup
in a table, which gives you much more flexibility in working with your data.
When you have two tables with a one-to-many relationship, the values of the
connecting field may be perfect for a lookup field. When you enter records
in the detail table (the “many” table in the relationship), the foreign key
(related field) needs to match the primary key of the master (“one”) table.
Consider making the foreign key in the detail table a lookup field, with the
primary field in the master table providing the list of possible values. If you
have a Products table (the master table) and an Order Detail table (the detail
table), make the Product Code field a lookup field, using the Product
Code field from the Products table as the list of values. (You can find more
information on relationships in Book I, Chapter 3, and in Chapter 6 of this