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.
I frequently copy tables into blank documents. When I do, I can't move the cursor to the top of the document to add blank lines just above the copied table. You can reproduce the problem quickly enough. Simply open a blank document and choose Insert from the Table menu. Choose Table and then OK (accepting the default table dimensions). Then, try to position the cursor above the table. It's a frustrating problem, unless you know the easy shortcut for accessing the top of the document. Do you know it?
Last week we asked…
"How can you show a total in an Access query?" It's a bit of a trick question because two traditional methods will work, but they return very different results. First, there's the Totals view, which uses the SUM aggregate function to return a total. The Totals view doesn't return the actual values, just the sum. Then, there's the SQL UNION operator, which you can use to add a SUM function to a series of values. In this way, you can return the values and the total.
A Totals view is simple to create but has a few quirks. You might see it referred to as a query, but it isn't a query in the true sense. To create a Totals view, do the following:
- 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:
UNION
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.