Best Method to Export Access Query/Report To Excel

This is related to an earlier question titled "MS Excel Enter a formula into multiple cells conditionally". Currently I create a query like "qrsCostOfSales" in the sample database attached. This is exported to Excel. Using the subtotal function in Excel customer totals are added. The COS % for each customer is manually added in Excel. I can do all this in an Access report. However I need to present this information to our users/clients in a spreadsheet. Exporting the spreadsheet results in the attached spreadsheet. The resulting spreadsheet requires manipulation and sometimes not all the calculated fields are preserved. Also the Access export of a report only allows exporting to the "xls" format. Not very efficient. My question is there a way to export this data from Access to Excel where the only work required in Excel would be formatting ? Thanks.
TestExcelExport.accdb
CostOfSales_Test.xls
StampITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dustin SaundersDirector of OperationsCommented:
So, you already have a query to get the values which means you can just union to the sums of those.  

Essentially, select * from your qrsCostOfSales query, then UNION those results to totals of those.  Then we group them together.

SELECT * 
FROM qrsCostOfSales
UNION SELECT qrsCostOfSales.CustID+' Total' AS CustID, '' AS CustName, '' AS Item, Sum(qrsCostOfSales.TotAmt) AS SumOfTotAmt, Sum(qrsCostOfSales.TotQty) AS SumOfTotQty, Avg(qrsCostOfSales.CostOFSales) AS AvgOfCostOFSales, Avg(qrsCostOfSales.[COS%]) AS [AvgOfCOS%]
FROM qrsCostOfSales
GROUP BY qrsCostOfSales.CustID, '', '';

Open in new window


Results:
unionResults.png

Add a GRAND TOTAL with another union to a sum of all.
SELECT * 
FROM qrsCostOfSales
UNION SELECT qrsCostOfSales.CustID+' Total' AS CustID, '' AS CustName, '' AS Item, Sum(qrsCostOfSales.TotAmt) AS SumOfTotAmt, Sum(qrsCostOfSales.TotQty) AS SumOfTotQty, Avg(qrsCostOfSales.CostOFSales) AS AvgOfCostOFSales, Avg(qrsCostOfSales.[COS%]) AS [AvgOfCOS%]
FROM qrsCostOfSales
GROUP BY qrsCostOfSales.CustID, '', '';
UNION SELECT 'GRAND TOTAL' AS CustID, '' AS CustName, '' AS Item, Sum(qrsCostOfSales.TotAmt) AS SumOfTotAmt, Sum(qrsCostOfSales.TotQty) AS SumOfTotQty, Avg(qrsCostOfSales.CostOFSales) AS AvgOfCostOFSales, Avg(qrsCostOfSales.[COS%]) AS [AvgOfCOS%]
FROM qrsCostOfSales;

Open in new window


unionResults2.png
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
If you want the totals to be "live", you will need to export the data and then using OLE automation, add the totals using VBA.  If I need to do things like this and I don't know the Excel VBA to do them, I open Excel and turn on the macro recorder and perform the steps.  Then I turn off the recorder and open the generated macro.  Sometime, it can be directly imported into Access but other times, you will need to clean up the way cells are referenced.  The generated code is from Excel's point of view and you need it to be from Access' point of view.  I don't have any code handy.
0
StampITAuthor Commented:
I replicated what you did with a couple changes. Instead of AVG I summed CostOfSales and divided to get the percent for customer and grand total. So it appears to work. The only issue is the customer totals are displayed at the top before the item by item summary. See attached. Yours is not like that. What am I doing differently ?  Thanks.
COS_EE.docx
0
PatHartmanCommented:
In order to get the totals to be at the bottom, you need a field you can sort on that will make that happen.  Then include an order by clause in the query.  It looks like sorting on CustID in the above example will work.  However, if the actual customer ID is not fixed in length, there will be anomalies because the numeric value is being converted to a text string and so it will sort like a string so you might have to solve that problem also.

For example
1
1
1Total
10
10
10Total
100
100
100Total
2
2
2Total
0
StampITAuthor Commented:
Thanks Dustin and Thanks Pat for the explanation on sorting.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.