Access 2010

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.
   The Query Design Command
2. In the Show Table dialog box that appears, select each table you would like to include in your query, then click Add. After you've added all of the tables you want, click Close. When we planned our query, we decided we needed information from the Customers and Orders table, so we'll add those.
   Selecting tables to use in the query
3. The tables will appear in the Object Relationship Pane, which is 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
4. The Join Properties dialog box will appear. Select an option to choose the direction of your join.
   • Choose option 2: for a Left to Right join. In our query, the left table is the Customers table, so choosing this would mean all of the customers who met our location criteria, whether or not they had placed an order, would be included in our results. We don't want to choose this option for our query.
   • Choose option 3: for a Right to Left query. Since our right table is our Orders table, selecting this option will let us work with records for all of the orders and only the customers who've placed orders. We'll choose this option for our query, since this is exactly the data we want to see.
   Changing the join direction to Right to Left
5. In the table windows, double-click the field names you would like 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, 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
6. Set field criteria by entering the desired criteria in the criteria row of each field. We want to set two criteria:
   • First, to find customers who do not live in Raleigh, we'll type Not In ("Raleigh") in the City field.
   • Second, to find customers who have a phone number beginning with the area code 919, we'll type Like ("919*") in the Phone Number field.
   Setting field criteria
   
7. After you have set your criteria, run the query by clicking the Run command on the Query Tools Design tab.
   Selecting the Run command
8. The query results will be displayed in the query's Datasheet View, which looks like a table. If desired, save your query by clicking the Save command on the Quick Access Toolbar. When prompted to name it, type in the desired name, then click OK.
   Naming the new query to save it