Wednesday, July 23, 2008

Creating a Totals row in a query


If you have ever built a query and wanted to display column totals like you would in Excel, it's been necessary to jump through a few hoops to achieve it. In Access 2007, the feature is built in.
To create a Totals row in Access 2007, do this:
  1. Create a Select query with the fields that you want to display. It can be a Totals query if you like, or just a straight query.
  2. Switch to Datasheet view, then select the Home tab and click the Totals (Sigma) button in the Records group. You will see a new row below your query, with the word Total in the first field.
  3. For each field that you want to summarise, select the drop-down that appears in that field and pick the summary function (Sum, Average, Count ...) that you want to use.
  4. The Totals row is a toggle: to hide it, click the Sigma button again to deselect it.
That's it. The example shows UK car sales data for 2004, grouped by country of origin.

To create a Totals row in earlier versions of Access, you need to get a bit more creative. This article shows one way to get the job done.

Denis Wright

No comments: