Adding a total row to a select query

Access doesn't offer an obvious way of including a grand total within a query and in most circumstances it is better to do this by displaying your data in a form or a report. There may however be a requirement to copy a set of records and include a total value as a final record.

The following example takes some booking records and adds a query total.

	more...			more ...

To achieve this it is necessary to create another table. Add a new table to your database, name it 'Temporary_Table' and give it a single numeric field called 'TempField1'. Add two records containing the values '1' and '2'.

Now build a new query containing both the main table of data (Bookings) and also the Temporary_Table. The tables cannot be linked. Select the Totals option from the query design ribbon (Design | Show/hide | Totals).

AddTotalToQuery : Select Query (Design)				ox
Field:	NewBkgRef: IIf([tempf...	NewUserGrp: IIf([tempf...	Invoice_Amount	Booking_Ref
Table:			Bookings	Bookings
Total:	Group By	Group By	Sum	Where
Sort:
Show:	Y	Y	Y	N
Criteria:				> 2448
Or:

Apart from the Invoice_Amount, all of the fields need to be user-defined:

• NewBkgRef: IIf([tempfield1]=1,[booking_ref],"Query total")
• NewUserGrp: IIf([tempfield1]=1,[usergroup_name],"-")
• NewDateOut: IIf([tempfield1]=1,[date_out],"-")
• NewDateRtn: IIf([tempfield1]=1,[date_return],"-")

The two records in the unlinked Temporary_Table cause the query to run twice, but the Group By instruction ensures that the second time it is summarised to a single record.

If you need to add criteria to this type of query, it is best to put them in separate (possibly duplicate) fields using the 'Where' totalling option. Otherwise they can interfere with the sum total.