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

5.5.1. 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;