Microsoft Office Tutorials and References
In Depth Information
Working with Query Datasheets
Creating AutoLookup queries
to fill in data automatically
AutoLookup queries can be terrific tools when you want to enter one value
(such as a customer number) and see other data from the same table (such
as the customer’s name, address, and phone number). You may want to use
this feature while you enter a new order: You can enter a customer number
and see the contact information, and then enter the particulars of the order,
such as the date and payment method. You can even create an AutoLookup
query and use it as the basis of a form, in which it may be more convenient
to enter data. AutoLookup queries may sound complicated, but in fact,
they’re pretty simple.
The AutoLookup feature also works in forms.
The key to creating an AutoLookup query is including the Join field from
the “many” side of the one-to-many relationship (also known as the foreign
key) . Then, when you enter a value for that field, Access fills in other fields
from the “one” side of the relationship automatically.
The query shown in Figure 1-15 displays fields from the Orders and Address
Book tables. The ContactID field comes from the Orders table (the key
field on the “one” side but displayed from the “many” table).
When new orders are entered in the query datasheet, only the customer
number needs to be entered. Access automatically fills in the first name, the
last name, and other contact information from the Address Book table. Then
you can add the rest of the order information.