Introduction

Using Totals in a Query

Sometimes, you may want to see your query results grouped or counted in some way. Access 2007 offers several options that make these functions possible. Perhaps the easiest of these is the Totals command, whose optional functions are very similar to the functions used in Microsoft Excel. These functions include:

• Sum -- used to add a column of numbers.
• Average -- used to find the average of a column of numbers.
• Maximum -- returns the highest value in a field.
• Minimum -- returns the lowest value in a field.
• Count -- used to count the number of same values in a query.

One of the most useful Totals functions to use in queries is the Count function.

Using Count and Group By in a Query

When you use the Totals command in a query, Access will automatically group every field by the values in each field. This just means that it will look for repeating values and group the like values together, so they appear as one record, rather than as many records. This is called the Group By function.
Notice that we get a record back for every order of each book that has been ordered.
In our bookstore example query, we want to see these titles grouped together, so that we see each ordered titles only one time. To do this, we use the Count and Group By options.

To Use the Count and Group By Options in a Query

To include the Group By and the Count functions in a query:

• Click on Totals in the Show/Hide group on the Ribbon.

• Next, click in the Totals row for the field you wish to count. We want to count the number of times the same Book ID appears in the Orders table.
• From the list of optional Totals functions, select Count.

• Click Run! to see your results. Notice that each title, author, price and category is now listed only one time for each book, with an extra column that indicates the number of times this Book ID appeared in the Orders table.

Sorting and Filtering Query Results

Once you have the results looking how you want them, you can sort and filter them to narrow your results down even further. This can be done using the methods of sorting and filtering covered in Lesson 10 and Lesson 11, or by applying the sort and filter in the query design itself.

To Sort via the Query Design

To add a Sort to any field in the query design:

• Click on the Sort row for the field you wish to sort. A drop down list will appear:
• Choose one of the options:
• Ascending will show your results sorted with the lowest numerical value or the text value closest to A first.
• Descending will show the results with the highest numerical value or the text value furthest from A first.
• (not sorted) will keep your records grouped, but not sort them.
• Click Run! to see the results.

To Filter via the Query Design

To add a filter to your query design:

• Click the Criteria row in the query design.
• Add your filter criteria by typing the value in the cell, as we did in the following example:

Challenge!

• Create a query that uses the Count and Group By options.
• Modify a query design to include a Sort
• Run the query and view your results.
• Save the query.
• Modify a query design to include filter Criteria.
• Run the query and view your results.