Hack #43. Place a Grand Total in a Query
Use a Union query to combine raw data records with the data total.
Here's a neat way to list the records in a table and have the total appear at the bottom. First, create a Select
query to return the table records; then use the Union
statement to combine them with the data total. The Sum
aggregate function handles returning the total. The assumption, of course, is that the data is numeric.
You need to enter this type of query in the SQL pane because the query grid doesn't support creating or displaying
Union
queries. Here is a sample SQL statement that combines sales records with the sum of the sales:
SELECT tblSales.Amount FROM tblSales UNION ALL SELECT Sum(tblSales.Amount) AS SumOfAmount FROM tblSales;
Figure 5-11 shows the bottom of the returned query records. Sure enough, a grand total is in the last record.
Figure 5-11. Including the total with the data
Hacking the Hack
You can easily modify this query to return other aggregate values, such as a count or an average. For example, here is the SQL from before, but modified to return the average:
SELECT tblSales.Amount FROM tblSales UNION ALL SELECT Avg(tblSales.Amount) AS AvgOfAmount FROM tblSales;
Get Access Hacks now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.