Access 2010

More types of queries

Video: Creating a Totals Query in Access 2010

Watch the video (2:09).

By this point, you should understand how to create a simple one- 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 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 about 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:

• Count, which counts the number of the same items in a field
• Sum, which adds the numbers in that field
• Average, which finds the average of the numbers that occur in that field
• Maximum, which returns the highest value that has been entered in that field
• Minimum, which returns the lowest value that has been entered in that field
• First, which returns the first, or earliest, value that has been entered in that field
• Last, which returns the last, or most recent, value that has been entered in that field

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, not 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.