Solved: Sending Access query to Excel

[hillaryruth]

I?m sending the results of this query to EXCEL.

It returns 17 rows (there are 17 DO) and 12 columns, the first column showing each DO and the last column showing the total across each row.

This works fine.

Unfortunately I'm a bit short on knowledge of VBA and only know enough to start being dangerous. Now I would like to add an additional row to return the total for each column. Can this be done and how would I do it?


I've been researching these forums trying to solve my problem but have not been able to successfully apply code examples I've seen to my database. I have attached a copy of my DB and if you could show me what to put where it would be greatly appreciated.

SELECT DISTINCTROW
Query1.DO,
Sum(Query1.[LESS THAN 100]) AS [LESS THAN 100],
Sum(Query1.[100 TO 5000]) AS [100 TO 5,000],
Sum(Query1.[5001 TO 10000]) AS [5,001 TO 10,000],
Sum(Query1.[10001 TO 15000]) AS [10,001 TO 15,000],
Sum(Query1.[15001 TO 20000]) AS [15,001 TO 20,000],
Sum(Query1.[20001 TO 30000]) AS [20,001 TO 30,000],
Sum(Query1.[30001 TO 40000]) AS [30,001 TO 40,000],
Sum(Query1.[40001 TO 50000]) AS [40,001 TO 50,000],
Sum(Query1.[50001 TO 100,000]) AS [50,001 TO 100,000],
Sum(Query1.[GREATER THAN 100,001]) AS [GREATER THAN 100,001], Sum(Query1.[LESS THAN 100]+
Query1.[100 TO 5000]+
Query1.[5001 TO 10000]+
Query1.[10001 TO 15000]+
Query1.[15001 TO 20000]+
Query1.[20001 TO 30000]+
Query1.[30001 TO 40000]+
Query1.[40001 TO 50000]+
Query1.[50001 TO 100,000]+
Query1.[GREATER THAN 100,001]) AS [TOTAL CASES],
FROM Query1
GROUP BY Query1.DO;

[XLGibbs]

You mean like a subtotal row? Won't happen in Access without some jumping through fire.

You might be able to structure a report in Access to do it, but the best way would be to query the data into an Excel pivot table using MS QUery and feed the results directly to the pivot table. That way you can do a ton of things.

[hillaryruth]

Thanks, I'll start reading on that