MeadInKent
| Feedback | Linking Tables in Queries |

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.


AddTotalToQuery: select query ox
NewBkgRef NewUserGrp NewDateOut NewDateRtn SumOfINVOICE_AMOUNT
2449 CHELSFIELD GIRL GUIDES 10 Oct 05 10 Oct 05 20.55
2450 CODELINK 08 Oct 05 09 Oct 05 37.50
2451 GODDINGTON SCHOOL 26 Oct 05 26 Oct 05 62.47
2452 HIGH FLYERS ATHLETICS 30 Oct 05 30 Oct 05 22.65
2453 GODDINGTON SCHOOL 16 Nov 05 16 Nov 05 27.30
2454 CHELSFIELD GIRL GUIDES 10 Nov 05 14 Nov 05 135.85
Query total - - - 306.32
 

 
UK more...   USA 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'.


2 Temporary_Table ox
TempField1
1
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
     tbl
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:

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.


file: accquerytotal.htm Page last updated Mar14 MeadInKent.co.uk 2014