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
But we need to analyse this data…
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.
||Here, the data has been grouped additionally by
region. Again the data is sorted automatically…
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
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