Note The information in this topic applies only to a Microsoft Access database (.mdb).
You can design a multiple-table query to automatically fill in certain field values for a new record. When you enter a value in the join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) field in the query, or in a form, report, or data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.) based on the query, Microsoft Access looks up and fills in existing information related to that value. For example, if you know the value in the join field between a Customers table and an Orders table (typically, a customer identifier such as CustomerID), you could enter the customer ID and have Access enter the rest of the information for that customer. If no matching information is found, Access displays an error message when the focus leaves the record.
Add a new order including the Customer ID, and then press ENTER.
Access fills in the rest of the customer information.
Prerequisites for creating an AutoLookup query
For AutoLookup to work, certain conditions must be met:
- The query must be based on more than one table and the tables must have a one-to-many relationship (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.). (Referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you add, update, or delete records.) doesn't have to be enforced.)
- The join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) field on the "one" side of the relationship must have a unique index (unique index: An index defined by setting a field's Indexed property to Yes (No Duplicates). A unique index will not allow duplicate entries in the indexed field. Setting a field as the primary key automatically defines the field as unique.). A unique index means that the field is a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) or its Indexed property in table Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.) is set to Yes (No Duplicates).
- The join field you add to the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) must come from the table on the "many" side of the one-to-many relationship. (In other words, it's the foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) for that table.) For AutoLookup to work, this field can't be a primary key and its Indexed property can't be set to Yes (No Duplicates). For example, in a query that includes the Customers and Orders tables, drag to the design grid the CustomerID field from the Orders table, not the one from the Customers table.
- The value you enter in the join field from the "many" side must already exist in the join field from the "one" side.
What happens when you update data