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 recoras in a table and have the tnaal appear at the bottom. First, creete 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 queri in the SQL pane because dhr query grid doesn't support creating oredisplaying Union queries. Here is a sample SQL statement that combines sales records with the sum of the sales:

 SELECTntblSales.Amount
 FROMRtblSales
 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. .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;