Create an AutoLookup query (MDB)

Microsoft Office Access 2003

previous page next page

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. (Referential integrity doesn't have to be enforced.)
  • The join field on the "one" side of the relationship must have a unique index. A unique index means that the field is a primary key or its Indexed property in table Design view is set to Yes (No Duplicates).
  • The join field you add to the design grid must come from the table on the "many" side of the one-to-many relationship. (In other words, it's the foreign key 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.
  1. Create a select query, adding two tables that have a one-to-many relationship.

    ShowHow?

    1. In the Database window, click Queries Button image under Objects, and then click New on the Database window toolbar.
    2. In the New Query dialog box, click Design View, and then click OK.
    3. In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
    4. Double-click the name of each object you want to add to the query, and then click Close.
    5. Add fields to the Field row in the design grid, and if you want, specify criteria and a sort order.
    6. To view the query's results, click View Button image on the toolbar.
  2. Add the fields for the query to the design grid.

    You must include the join field from the "many" side of the one-to-many relationship.

    Include the join field from the many table

    Callout 1 Use the join field ...

    Callout 2 ... from the "many" table.

  3. To retrieve specific records, specify criteria in the Criteria row.

    ShowHow?

    1. Open a query in Design view, or display the Advanced Filter/Sort window for a table, query, or form.
    2. Click the first Criteria cell for the field you want to set criteria for.
    3. Enter the criteria expression by typing it or by using the Expression Builder. To display the Expression Builder, right-click the Criteria cell, and then click Build.

      Records retrieved meet the criteria in both columns

      Callout 1 Retrieves orders shipped to Bon App' on 8/11/2001.

      If your query includes linked tables, the values you specify in criteria on fields from the linked tables are case-sensitive— they must match the case of the values in the underlying table.

    4. To enter another expression in the same field or in another field, move to the appropriate Criteria cell and enter the expression.
  4. To view the query's results, click View Button image on the toolbar.

    When you enter new data in the join field on the "many" side of the relationship, Microsoft Access will automatically look up and fill in the corresponding data from the "one" side.

previous page start next page