AutoLookup in Multitable Queries
The AutoLookup feature is automatically available in
Access. As you fill in key values on the “many” side of a one-to-many
relationship in a multitable query, Access automatically looks up the
non-key values in the parent table. Most database developers refer to
this as enforced referential integrity.
A foreign key must first exist on the “one” side of the query to be
entered successfully on the “many” side. As you can imagine, you don’t
want to be able to add to a database an order for a nonexistent
customer.
For example, I have based the query in Figure 5 on the Customers and Orders tables. The fields included in the query are CustomerID from the Orders table; Company, Address, and City from the Customers table; and Order ID and Order Date from the Orders table. If you change the CustomerID field associated with an order, Access looks up the Company, Address, and City fields from the Customers table and immediately displays them in the query result.
Notice in Figure 6 how the information for Company F is displayed in the query result. Figure 7 shows that the Company and Address fields change automatically when the Customer field is changed to Company C.
Don’t be confused by the combo box used to select the customer ID. The
presence of the combo box within the query is a result of Access’s
Lookup feature. The customer ID associated with a particular order is
actually being modified in the query. If you add a new record to the
query, Access fills in the customer information as soon as you select
the customer ID associated with the order.
