Calculating Totals in Access Queries

An often-overlooked feature of the ordinary select query is its ability to calculate totals. Whilst the crosstab query is useful for analysing complex data, it needs several fields to work with. Supposing the data you wish to analyse is contained in just two fields? The answer is to create a select query and make use of the totals option.

Totals can do more than just add up too! Here's how it works…

We have a table containing a mass of sales data. The records are arranged in no particular order, and there are several fields containing information about each individual sale…

If we wanted, we could do a sophisticated analysis of this data with a crosstab query, but to begin with all we want to know is "What are the total sales for each person?" All the data we need is contained in just two fields, Salesperson and Sales, and a crosstab query needs at least three fields to work with. No problem! We're going to use Totals.
 

Adding Totals to a Query

The first step is to create a query selecting the fields we are interested in. Running the query at this stage simply presents with a list of data. In this case Salespeople and numbers representing individual Sales. 

But we need to analyse this data…

In the design view of the query we activate the totals option. To do this either click the Totals button on the toolbar, or choose View > Totals from the menu, or right-click anywhere in the QBE grid and choose Totals from the shortcut menu.

Activating the Totals option creates another row in the QBE grid, the Total: row. The default setting for the Total option for each field is Group By, but to make sense out of this data we must change one of these and select a type of calculation. In this example we want to calculate the sum of Sales for each Salesperson Clicking in the Total cell of the Sales column, then clicking the down-arrow that appears, reveals a list of choices. Sum is the one we need here, but you can see that several useful calculations can be chosen, mathematical and statistical. Click the appropriate option to select it and run the query.

The result of the query shows a total sales figure for each salesperson. The Salesperson field is automatically sorted into alphabetical order so there's no need to choose a sort option in query design. If you wish you can override the A-Z sort order and choose Descending if you want the results sorted the opposite way.

Refining the Query

You can still add criteria to the query definition if you wish. Here, criteria have been added to the Salesperson field to select records for specific salespeople…

Grouping by Several Fields

You can group my as many fields as you wish, providing the chosen fields contain suitable data.

Using Additional Criteria

It may be that you want to specify certain criteria referring to another field, but you don't want to group by that field or calculate its data. To do this, select the Where option in the Totals row. This lets you add criteria to the criteria row of that field to further refine your query. When you do this Access unchecks the Show box for that field. If you want to see the new field in the result of your query, you must add to the QBE grid a second time (with Grouped By selected in its Totals row). Construct the query like this…

The Region field has been added to the QBE grid twice, first using the Where option to specify criteria, and again using Group By to let the result be shown.

 

Here's the result. The data has been grouped by Salesperson and Sales subtotalled (summed) for the North region only.

Note: Because what you see when you run your query using Totals is a summary of your data, it doesn't behave like a normal dynaset. Changes made to the data here are not reflected in the original data.

©2000 Martin Green - www.fontstuff.com - martin@fontstuff.com - All rights reserved