Using Queries to Make Data Meaningful - Part 2

In Access queries let you retrieve information from one or more tables on a set of defined search conditions. Learn how to use queries in Access here.

Introduction

Access 2007 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

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:

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:

Ordered BooksQuery 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:

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:

To filter via the query design:

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