Microsoft Office Tutorials and References
In Depth Information
Creating a Lookup Field
Using the Lookup Wizard
An easy way to create a lookup field is to use the Lookup Wizard. In this
section, we show you how to use the Lookup Wizard to enter the Customer
ID number (stored in the Address Book table) in the ContactID field of
the Orders table. The Orders table lists information about each order, one
record per order. Fields include the order date, the contact ID, payment
information, and shipping information. Items ordered are stored in the Order
Display the table that you want to contain the lookup field in Design view
and then follow these steps:
1. In the top half of Design view, find the field that you want to contain
the drop-down menu.
2. Click the down arrow to display the Data Type drop-down menu, and
choose the Lookup Wizard option.
3. For this example, view the Orders table, and change the data type of
the ContactID field.
Access launches the Lookup Wizard.
4. Tell the wizard whether the values that you want to appear on the
field’s drop-down menu come from a field in another table or from a
list that you type; then click Next.
Storing the values in a table is easier in the long run, even if you have to
cancel the wizard and create a new table.
If you don’t want the drop-down menu to display every value in the field
in another table, you can base the drop-down menu on a field in a query.
(Find out all about queries in Book III.) If you want to retain
discontinued products in the Products table but not allow those products to be
entered in new orders (that is, the lookup list), you could create a query
that displays only products that are currently available.
If you tell Access that you want to type the values, a table appears in
which you can type the lookup list. Click the table in the wizard window
(which currently has only one cell), and type the first entry in the list.
Press Tab — not Enter — to create new cells for additional entries, and
skip to Step 9 when you finish.
5. Choose the name of the table (or query) that contains the data that
you want to appear in the drop-down menu; then click Next.
If you want to see queries, click the Queries button. Click the Both
button to show the names of both tables and queries.
6. Tell Access which field(s) you want to display in the drop-down
menu by moving field names from the Available Fields list box to the
Selected Fields list box; then click Next.