More Query Design Options

In Access query design is essential for getting the data you want. Use query design in Access to design almost any query you want.

Challenge!

  1. If you haven't already, download our sample database and open it.
  2. Open the Customers Who've Ordered from Nearby Towns query, and switch to Design view.
  3. Add a Totals row to the query.
  4. Set the Totals row in the Orders Table ID field to Count. This will let us count how many orders each customer has placed.
  5. In the Customers table in the Object Relationship pane, double-click the word City to add another City field to the design grid below.
  6. Click and drag the City field you just added so it is to the left of the First Name field. It should now be the leftmost field in the design grid.
  7. Apply the following multilevel sort:
    • In the leftmost City field, apply an ascending sort.
    • In the Last Name field, apply an ascending sort.
  8. Hide the leftmost City field.
  9. Run the query. If you did it correctly, there should be 14 records in the query results. The first record should look like this.