Access 2007

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 refine the results even more.

Using Queries - Part 2

Watch the video! (4:45min)

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

Take our bookstore database for 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:

Query for Ordered Books

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

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

• Click on 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 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.
  
  
  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 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:
  
  
  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 furthest 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

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:
  
  
  Filtering via Query Design
• Click Run! to see the results.

Challenge!

If you haven't already done so, save the sample Ready2Read database on 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 Criteria.
• Run the query, and view your results.
• Save the query.