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:
- Create or open a query you want to use as 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.
- In the Query Design tab, locate the Show/Hide group, then select the Totals command.
The Totals Command
- 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, and click the drop-down arrow that appears.
Selecting the totals row of the field we want to perform a calculation on
- 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
- When you are satisfied with your query design, select the Run command on the Query Tools Design tab to run the query.
The Run Query command
- 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.
Saving the totals query. Note the sums in the far-right field.