Designing a Multi-table Query

When designing an Access query it can be simple or more complex. In Access complex query guidlines let you do more with data.

Creating a multi-table query

Now that we've planned our query, we're ready to design and run it. If you have created written plans for your query, be sure to reference them often throughout the query design process.

To create a multi-table query:

  1. Select the Query Design command from the Create tab on the Ribbon.
    Clicking the Query Design Command
  2. In the dialog box that appears, select each table you want to include in your query and click Add. You can press and hold the Ctrl key on your keyboard to select more than one table. When we planned our query, we decided we needed information from the Customers and Orders tables, so we'll add these.
    Selecting tables to use in the query
  3. After you have added all of the tables you want, click Close.
  4. The tables will appear in the Object Relationship pane, linked by a join line. Double-click the thin section of the join line between two tables to edit its join direction.
    Clicking the join line to edit its direction
  5. The Join Properties dialog box will appear. Select an option to choose the direction of your join. In our example, we'll choose option 3 because we want a right-to-left join.
    The Join Properties dialog
  6. In the table windows, double-click the field names you want to include in your query. They will be added to the design grid in the bottom part of the screen. In our example, we'll include most of the fields from the Customers table: First Name, Last Name, Street Address, City, State, Zip Code, and Phone Number. We'll also include the ID number from the Orders table.
    Adding table fields to the query
  7. Set field criteria by entering the desired criteria in the criteria row of each field. We want to set two criteria: Not in ("Raleigh") in the City field, and Like ("919*") in the Phone Number field. This will find customers who do not live in Raleigh but who do live in the 919 area code.
  8. Setting field criteria
  9. After you have set your criteria, run the query by clicking the Run command on the Design tab.
    Clicking the Run command
  10. The query results will be displayed in the query's Datasheet view, which looks like a table. If you want, save your query by clicking the Save command in the Quick Access Toolbar. When prompted to name it, type the desired name, then click OK.
    Naming the new query to save it

Now you know how to create a multi-table query. In the next lesson, we'll talk about more query design options that can make your query even more powerful.