News, Tips, and Advice for Technology Professionals

in Microsoft Office , in Data Management on June 15, 2009, 3:01 AM PST

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):

Sometimes, you'll want to see a summary of the data along with all the data. When that's the case, use a UNION operator as follows:

Using the same example query, click SQL View (in Query Design view).
Add the UNION operator as shown below: UNIONSELECT "Total", Sum(UnitPrice*Quantity)FROM [Order Details]
Click Run to see the results.

The last row displays a total for all the orders. That's because the sort order pushes the Total text field list to the bottom of the resultset. That won't always be the case, and in fact, displaying the UNION SELECT row can be a bit troublesome. For more information on using UNION in this way, read How do I... Display summary values in Microsoft Access using UNION? Thanks to everyone who replied. A couple of members suggested rather unique solutions:

Iansoady suggested a nested query.
Mark recommended the DSUM() domain aggregate.

Mark and Tmiller both recommended the UNION query, which is one of the traditional solutions. Thanks to songsu for mentioning 2007's new Totals button, which makes UNION, within this context, obsolete.