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.