DataWright Information Services

Consulting and Resources for Excel and Access

A query with a Totals row

Ever need to create a query with a Totals row, like in an Excel report? Use this sample to see how.

Versions prior to 2007

I have seen two methods for creating a query with a totals row. The first involves creating a normal Select query joined to a one-row Totals query with a Union query. The second is more straightforward to maintain, as everything is done in a single query. The example below illustrates the second approach.

First, create a dummy table. It consists of a single numeric field, with two records containing the values 1 and 2.

Now create your query design, with the tables that you need for your query. Add the dummy table to the grid but don't link it to anything. This creates a cartesian join; all of the query records will be duplicated and will contain either 1 or 2 in the dummy field.

With the exception of the field(s) to be totalled, all other fields need to be created as custom expressions. For the CountryOfOrigin field in this query, the expression is:

CompanyHQ:IIf([DummyField]=1,[CountryOfOrigin],"Total Sales")

Note that it's a good idea to add extra fields for filtering and sorting, just in case these operations affect the expressions in the display fields. In this example there are two helper fields: DummyField, sorted Ascending, and Year, filtered to 2004.

To finish, format the data in the CarSales field. In this case the format is #,##0 which displays whole numbers with commas for the thousands.

Because the query is a totals query, the TotalSales row that appears every time DummyField has a value of 2, is grouped down to a single row. Sorting DummyField ensures that this Totals row will apear at the bottom of the query, where you want it.

The SQL for the finished query is

SELECT IIf([DummyField]=1,[CountryOfOrigin],"Total Sales") AS CompanyHQ, 
   Sum(UK_CarSales.Sales) AS CarSales
FROM tblDummy, Manufacturers 
INNER JOIN UK_CarSales ON Manufacturers.ManufacturerID = UK_CarSales.ManufacturerID
GROUP BY IIf([DummyField]=1,[CountryOfOrigin],"Total Sales"), tblDummy.DummyField, UK_CarSales.Year
HAVING (((UK_CarSales.Year)=2004))
ORDER BY tblDummy.DummyField;

Access 2007

Access 2007 has a simpler method for creating totals, and you can add them to any datasheet.

First, create your query. Then select the Home tab and, in the Records group, select Totals.

The Records group with the Totals item selected

For each column you can select from the standard list of summary functions; Sum, Average, Count, StdDev, Var, etc. You can toggle the totals on and off by clicking the Totals button, and Access will remember your previous settings for that datasheet.

If you filter records the totals will update; if the datasheet is too long for the screen the totals row will display at the bottom of the screen.