|   Home    |    Excel    |    Access   |   Word   |   Outlook   |   FrontPage   |   VBA   |   Downloads   |   Index   |
Calculating Totals...

 

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…

Source table for the calculations

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

The select query The select query - result

But we need to analyse this data…

Query totals buttonIn 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.

 

QBE grid with totals selected
 

Choose the type of calculation

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.

Totals using the Sum function - result

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.

^ top


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…

Adding criteria Adding criteria - result


Grouping by Several Fields

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

Grouping by two fields

 
Grouping by two fields - result Here, the data has been grouped additionally by region. Again the data is sorted automatically…

^ top


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…

Gruped by two fields with additional criteria

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.

 
Grouped by two fields with additional criteria - result 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.

^ top
   

 

 

 

 

Hit Counter