You can use additional select queries to perform calculations on your records. These totals queriesuse aggregate functions, a difficult term for doing some easy math. For example, you use the Average function for calculating the average of a group of numbers. The Sum function is also self-explanatory; it finds the sum.

The Count function is only slightly harder to understand. You use it to count the number of records that have various traits. For example, suppose you’re a sales manager and want to know how many orders you’ve shipped to each country. You could have Access count the records in your orders table and group them by each country in the Ship Country (or similarly named) field.

Often you’ll want to restrict the count to specific criteria. For example, you may want to know the number of orders sent to each country that were shipped before, on, or after a certain date. You can add criteria and use expressions to refine your query and obtain these results.

 What You Count & What You Show

None of these tasks is difficult to execute, as you will see presently. But there is one aspect of designing totals queries that requires some thought. When you do use criteria, there are two ways to refine the query and limit the records displayed.

First, you can count all the records and then limit those you retrieve and display. For example, you could count the orders for each country and then show only those countries that had more than 50 orders.

As an alternative, you could limit the number of records actually counted in the first place. For example, you could count only those records shipped by a certain carrier and show all the records you counted. You can also combine both types of criteria. With apologies to Abe Lincoln, we might describe it as showing some of the records some of the time.

An example will make it much easier to understand these techniques. So let’s take a look at how to use totals queries with criteria.

Scroll horizontally through the fields. In the Shipped Date field, note that orders were made in 1996 and afterward. The last column, Ship Country, shows the country of destination. Close the Orders table.

In the Database window, with the Orders table still selected, click the Insert menu and then click Query. In the New Query window, click Design View and click OK. Maximize the Select Query window. Click the View menu and then Totals to display the Total row on the Design Grid. (View, Totals is a toggle that displays or hides the Total row.)

 Count All Records & Show Some Of Them

Let’s count all the orders by country and display only countries that have more than 50 orders. We’ll first add a column to group the records by country. In the Orders box, double-click ShipCountry (the last field on the field list) to add it to the grid. The Total row is set to the default GroupBy.

Now we’ll add a column to count the records. Double-click OrderID; click in the Total row of the OrderID column, click the down-arrow button, and choose Count. Because each order has its own OrderID, counting OrderIDs will count all the records.

Click the View button to see a breakdown of all orders by country. As you view the results, note that five countries have more than 50 orders: Brazil (83), France (77), Germany (122), UK (56), and USA (122).

Click View again to return to Design view. Click in the Criteria row of the OrderID column. Type >50 and click view. Click the View button, and your table should now display the five countries with more than 50 orders.

 Count Some Records & Show All Of Them

Click View again. Delete the >50 from the Criteria row of the OrderID column.

Now let’s restrict the number of orders counted and show all the results. Double-click OrderDate to add it to the third column of the design grid. Click your cursor into the GroupBy cell in the Total row. When you see the down-arrow button, choose Where, the last selection on the list. Click in the Criteria row of the OrderDate column and type >12/31/96. Adding this criteria will restrict the orders counted to those that have order dates in 1997 and later.

Click View. Note that the number of countries with more than 50 orders now includes Brazil (70), France (62), Germany (98), and the USA (99) but no longer the UK (46).

 Count Some Records & Show Some Of Them

Click View. Now let’s display just the countries with more than 50 orders made after Dec. 31, 1996. Click in the Criteria row of the OrderID column and type >50. Click View to see the four countries with more than 50 orders. In this case, you’ve used both types of criteria: you’ve counted only those orders made in 1997 and afterward, and you’ve displayed only those countries with more than 50 orders.

You can save this query if you choose by clicking File and Save or dump it by closing the Query window and clicking No in the dialog box.