- Base a new query on the appropriate table.
- To the query grid, add the column you want to total.
- Choose Totals from the View menu. Doing so will expose a Total row in the design grid. This row applies the Group By aggregate to all columns, by default. To return a total, select SUM from the Total row's drop-down list for that column. For instance, the following query groups by the OrderID value:
Click Run to see the results. There's one record for each OrderID value, although most orders comprise more than one item. The second column totals the order (UnitPrice * Quantity):
- Using the same example query, click SQL View (in Query Design view).
- Add the UNION operator as shown below:
SELECT "Total", Sum(UnitPrice*Quantity)
FROM [Order Details]
- Click Run to see the results.
- Iansoady suggested a nested query.
- Mark recommended the DSUM() domain aggregate.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.