Introduction

You already know how to plan and run a basic query. This lesson will show you how to run a query that includes a Totals function to group and count the records in the results. It will also deal with ways to further sort and filter via your query design to further refine your results.

Using queries: Part 2

Download the example to work along with the video.

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 to make these functions possible. Perhaps the easiest of these is the Totals command, whose optional functions are similar to the functions used in Microsoft Excel. These functions include:

• Sum , which is used to add a column of numbers
• Average , which is used to find the average of a column of numbers
• Maximum , which returns the highest value in a field
• Minimum , which returns the lowest value in a field
• Count , which is 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 functions 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 means it will look for repeating values and group like values together so they appear as one record rather than as many records. This is called the Group By function.

Let's use our bookstore database as an example. If we run a query to see the information for every book that has been ordered, we'd get a list that looks like this:

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 we see each ordered title 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:

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

  
  

  
  

  Totals Command
  
  The Total row will instantly appear in the bottom portion of the query design screen.

  
  

  
  

  Total Row in Query Design
• Click in the Totals row for the field you want 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 .

  
  

  
  

  Total Options in Query Design
• 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 the Book ID appeared in the Orders table.

  
  

  
  

  Count and Group By Functions in Query Results

Sorting and filtering query results

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

To sort via the query design:

• Click the Sort row for the field you want to sort. A drop-down list will appear:

  
  

  
  

  Sorting via Query Design
• Choose one of the options:
  • Ascending will show the 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 farthest from A first.
  • (not sorted) will keep your records grouped but will not sort them.
• Click Run! to see the results.

To filter via the query design:

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

  
  

  
  

  Filtering via Query Design
• Click Run! to see the results.

Challenge!

If you haven't already done so, save the sample Ready2Read database to your computer.

• 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 c riteria .
• Run the query, and view your results.
• Save the query.