Access 2013
More Query Design Options
More types of queries
By this point, you should understand how to create a simple one-table or multi-table query using multiple criteria. Additional queries offer you the ability to perform even more complex actions with your database. One of these is the totals query, which lets you perform calculations with your data.
Totals queries
Sometimes setting simple criteria won't give you the results you need, especially when you're working with numerical values. You may want to see your query results grouped or counted in some way. For example, let's say we want to find out how many of each menu item at our bakery has been ordered—how many Almond Croissants, Apple Pies, and so on. To do this, we could create a totals query to find the sum of the quantities for each item.
First, the totals query will group all similar menu items from separate orders (for example, Almond Croissants). Then, the Sum function will add the values in the Quantity field to calculate the total number sold for that item.
The Sum function helped us find the desired information in this example, but in other situations you may need to use a different function to find the answer you need. There are several different functions you can choose from:
- Count: Counts the total number of each item
- Sum: Adds the values together
- Average: Finds the average of the values
- Maximum: Returns the highest value
- Minimum: Returns the lowest value
- First: Returns the first, or earliest, value
- Last: Returns the last, or most recent, value
In our example above, we created a subtotal for each menu item in our query. If you wanted to create a grand total for all of the items, you would need to add a totals row. Review our lesson on Modifying Tables to learn how.
