Access 2007

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