In Access query design is essential for getting the data you want. Use query design in Access to design almost any query you want.
By this point, you should understand how to create a simple 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.
Sometimes setting simple criteria won't give you the results you need, especially when you're working with numbers. You may want to see your query results grouped or counted in some way. Access 2010 offers several options that make these functions possible. Perhaps the easiest of these is the Totals command.
When you use the Totals function in your query, the data in your fields will be grouped by value, meaning all items of one type are listed together. For instance, in a totals query on the items sold at our bakery, each type of item sold would be listed on a single row, no matter how many times that item had been sold.
Once your records are grouped, you can perform calculations with them. These calculations include:
These calculations will apply to the rows containing your grouped items. For example, if you decided to use Sum to find out how many of each item on a menu has been ordered, you would get a subtotal for each item in your query rather than a grand total of all of the items combined.
To add a calculation like a grand total to your query or table, review the instructions for creating a Totals row in our Modifying Tables lesson.