Microsoft Access 2007/2010 both have the new and very welcome Totals feature available in tables and queries. This article describes how to use this feature effectively, particularly in queries, where you can now easily display how many records meet query criteria in additional to listing the records.

Totals in Tables

Suppose you have an Access table with records containing numerical, dates and text data. To add a totals column to the table you just click the Sigma Totals symbol in the Home tab. The Totals row appears right at the bottom of the table. If you then click into any one of the fields in the Totals row, you'll see a pop down appears. Select this and you can choose how the Total for that column works. Access changes the available options depending on the data type in the field.

So for example if you select a field containing numbers, the pop down shows a range of arithmetic operators such as Sum, Average, Count, Max and Min. If you select a text field the Totals options only offers Count. This is because you cannot carry out calculations such as sum or average on text data, but you can count how many records there are contain text data. Counting text fields can be useful if not all text fields contain values. If you apply the Count Total to the primary key field, the count will show how many records there are in the table because there is one unique primary key value for each record in the table.

So you can use the Totals row in an Access table to show different Total analysis for each field. If you then apply a filter to the table, the Totals row will update to show the Totals for the filtered records, so the Totals row always show the correct analysis for the currently visible data.

If you then save the table, the Totals row is saved too. However as is usual with Access, the filter is not saved. So if you save a table with Totals row turned on and a filter applied, close the table, and then reopen it, the table will open with the Totals row still applied but with all table records showing.

Totals in Queries

However the Totals row feature really comes into its own when used in a query, because you can save the Total row analysis, as well as the query criteria. The Totals feature is turned on after a query is run, rather than in query design view. So you run the query and then turn on the Totals row feature in datasheet view. You click on the Totals icon in just the same way as for a table. In a query the Totals row is saved as part of the query layout rather than its design view.

So for example suppose you create and save a query based on the table you just looked at. You might choose a particular sort order and field criteria in your query. When the query is run you'll see the results. You can now click the sigma Totals icon and select the Totals you wish for as many query fields as required. By the way you can also Total a calculated field in a query. Once you're happy with the query results and the Totals row analysis you can save and close the query. Then run the query again and you'll see the Total row results as before. If you change any of the Totals row options, save the changed query layout before the query is closed.

In earlier versions of Access it was a difficult task to show how many records resulted from a query. Access could always list all the records which met the criteria, but it required additional grouping and counting tasks to actually show how many records met the criteria. Now with the new Total row feature all that has changed. You can now run simple queries, change the criteria, run more complex queries such as parameter queries safe in the knowledge that the Totals row will always display the correct results for the visible data.

Interested in learning more about Access 2007/2010? A really effective way is to attend a training course. This can enable you to gain lots of new skills in a short time, and then really boost your Access skills.