Grand Total Sum at the end of a query

[sportdlockport]

How can I add a grand total sum at the end of a detailed query without using a report. Is this possible in Access?

 

[grande]

To the best of my knowledge, you'll have to make a summing query which runs off the other query.

-------------------------
Just call me Captain Awesome.

 

[Golom]

Something like

(Select SomeField, TheNumber From tbl)

UNION ALL

(Select 'Total', SUM(TheNumber) From tbl)

That doesn't guarantee that the "Total" line will be the last line. You would need to use some "Order By" clause to sort it to the end of the list.

 

[Crowley16]

*I think* that if you have no sorts in the union query, then the results of the 2nd query will just be appended to the first query, i.e. it will be added to the bottom of the report.

but a better option if you're not using the query as a direct display, would be to create a virtual field in your first query, and name it total, and just give it "" as a value, then in your 2nd query, you can put the total in there.

--------------------
Procrastinate Now!

 

[tonyflavell]

Hi there

Just an idea but say you had an [InvoiceDate] field in your query, you could create a field (say [Total]) in your query with the expression

DSum("[FieldToSum]","Domain", "[Invoice Date] < " & Now())

This would create a running total in the "Total" field for every record as the "Invoice Date" would always be less than Now()

I have not tried this but I don't see why it wouldn't work!

Regards

Tony