'Total rows' in query

[sk88]

How do I make the 'total rows' in query permanently visible? Right now, every single time when I click on to my query, I will have to go to HOME---> RECORDS ---> TOTALS then the Total row will appear. And when I export that query, the TOTAL ROW is missing.



Does anyone have any idea on how to make the total rows visible always?

[Robeen]

When I have needed to export the data from queries along with Counts / Totals / Averages etc, I have created reports and put all the Counts, Totals Averages at the bottom of the reports & then exported the Reports. That way all the totals go out along with the data.

[sk88]

When I have needed to export the data from queries along with Counts / Totals / Averages etc, I have created reports and put all the Counts, Totals Averages at the bottom of the reports & then exported the Reports. That way all the totals go out along with the data.

Thanks Robeen.
I am trying that now but how do I add the total row to the report and how do I export?


I have this code already for exporting queries.

                           
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qryActivity", _
                               FileName:="C:\Exports\qryExportMetrics.XLS")

Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qryCalled911", _
                               FileName:="C:\Exports\qryExportMetrics.XLS")

Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qryCalled911Activity", _
                               FileName:="C:\Exports\qryExportMetrics.XLS")

Do I just change the TABLENAME for exporting report ?

[rpeare]

if you're doing an export to a .xls file I'd suggest changing it to a .csv (comma separated value) file. In that you can export your rows line by line and create formulas in the fields you want to do your summation in, create sub total line etc.

[Robeen]

In your report, right-click a field and go to Total - Count Records.
That will put a new row at the bottom with a count of the records in your report.

You can also very easily add totals & subtotals etc.

I usually set up a Macro with an 'ExportWithFormatting' Action [I'm using 2010] - and specify the Report as the object to be exported. It will ask for the type of file you want to export & the location.

This method has proved very easy, flexible & reliable for me.