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.

To create a totals query:

For our example, we want to find the total number we've sold of each of our menu items, so we'll use a query showing us all of the menu items we've sold. If you want to follow along in our database, open the Menu Items Ordered query.

  1. Create or open a query you want to use as a totals query.
  2. From the Design tab, locate the Show/Hide group, then select the Totals command.
    Clicking the Totals command
  3. A row will be added to the table in the design grid, with all values in that row set to Group By. Select the cell in the Total: row of the field you want to perform a calculation on, then click the drop-down arrow that appears.
    Selecting the totals row of the field we want to perform a calculation on
  4. Select the calculation you want to be performed in that field. In our example, we want to add the quantities of products we've sold, so we'll select the Sum option.
    Setting the Totals calculation to Sum
  5. When you are satisfied with your query design, select the Run command on the Query Tools Design tab to run the query.
    Clicking the Run command
  6. 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 on the Quick Access Toolbar.
    The totals query. Note the sums in the far-right field.