Here's the solution to last week's challenge on showing a total in an Access query. This week, we're testing your Word expertise: If a table appears at the beginning of a document, how do you add blank rows above the table? Try it — you might find it's not as easy as you think.
- 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.