none
Totals, Average etc in a query - can it be exported to Excel? RRS feed

  • Question

  • Hi

    I really like the functionality in Access queries. It gives you the totals, averages etc.
    When I export that kind of queries to Excel files the totals are gone.

    Is it possible to keep them?

    qrySumLiterSumCost

    <tfoot></tfoot>
    Tank Liter SumCost
    CCT04 2720 366,243264
    CCT04 2700 366,243264
    CCT07 2510 209,7722035
    CCT07 2500 218,5922035
    CCT11 2660 219,7975225
    CCT11 2660 219,7975225
    CCT12 2770 242,8577975
    CCT12 2800 242,8577975
    CCT13 2480 209,7722035
    CCT13 2500 209,7722035
    CCT22 2460 210,4943535
    CCT22 2540 210,4943535
    CCT22 2520 210,4943535
    CCT22 2470 210,4943535
    Total 36290 3347,683396


    Cheers // Peter Forss Stockholm


    Wednesday, September 11, 2019 10:59 AM

Answers

  • You can certainly pass the totals to a table and then export from there to EXCEL. Use an APPEND query to write the initial values and then use an UPDATE query to update them whenever you want.
    • Marked as answer by ForssPeterNova Wednesday, September 11, 2019 2:15 PM
    Wednesday, September 11, 2019 1:13 PM

All replies

  • The Total/Avg/Max/Min is calculated on the Fly...it doesn't retained in the Recordset (probably you have an unbound textbox with source : =Sum([SumCost])...so you cannot export it just by one click

    You need some extra code to open the Resulting Excel....find out the end of rows and just "plant" the Totals (better to let Excel do the job.... :)  )

    Wednesday, September 11, 2019 11:26 AM
  • You can certainly pass the totals to a table and then export from there to EXCEL. Use an APPEND query to write the initial values and then use an UPDATE query to update them whenever you want.
    • Marked as answer by ForssPeterNova Wednesday, September 11, 2019 2:15 PM
    Wednesday, September 11, 2019 1:13 PM
  • Thanks Lawrence

    So simple. But I did not see it.


    Cheers // Peter Forss Stockholm

    Wednesday, September 11, 2019 2:20 PM
  • No charge Peter...glad to help LOL.
    Wednesday, September 11, 2019 5:20 PM