Grand Total Query (1 Viewer)

batesonm · Jan 4, 2013

Hello All,

I have been searching through the forums for something that would be able to answer my issue but with no success :banghead:.

I would appreciate if someone could point me to the right thread or take a look at my issue.

I have the following query:

SELECT [In Hours].Outcome, Count([In Hours].Outcome) AS CountOfOutcome
FROM [In Hours]
GROUP BY [In Hours].Outcome;

Which gives me the following table that is exported to excel.

In Hours OverviewOutcomeCountOfOutcomeAnswer18323Assessor Unavailable20775Caller Disconnected703Engaged208Partner Returned240Remained in IVR4090Ring No Answer140Transfer to Partner3881

The issue I have is that I need there to be a grand total at the bottom of the table rather than Excel having to do it. Is this possible?

Thanks for looking and thanks in advance for any help.

pr2-eugin · Jan 4, 2013

Have you looked into Reports? Look into the link by Microsoft tutorial on how to create one..

Isskint · Jan 4, 2013

You could add an extra calculated field to sum the total, but this value would appear on each row of data.
Another way is a seperate query to return the grand total.
Alternatively you could use automation. If the export is done with the TransferSpreadsheet method, you could write some code to put the formaula =Sum(?:?) in the next row after the data.

batesonm · Jan 4, 2013

Thanks for swift reply!

@pr2-eugin - I am steering clear of reports as need the query to be usable in Excel but thanks for the link

@Isskint - The calculated field is an idea but it would look a bit messy with it being on every row. I had thought about doing an additional query for it but this is already running off another query and don't want to over complicate things.

The automation intrigues me, it is current exported using TransferSpreadsheet, would the code be written in the Access Macro or within the Excel spreadsheet?

Isskint · Jan 4, 2013

You would need to write this in Access, but you would need to change from macro to VBA. There is no way (as far as i know) to do this from a macro.

So essentially you would have a Sub - lets call it TransferTheQuery - that carries out DoCmd.TransferSpreadsheet. Then you would set a reference to the spreadsheet and the tab, calculate how many rows are on there to find the next empty row and add the formula.
BobLarson has some great examples of interacting with Excel from Access (Automation) at this website http://www.btabdevelopment.com/ts/default.aspx?PageId=10

batesonm · Jan 4, 2013

Thanks Isskint,

Reading that has blown my mind! I am thinking it may be easier to do another query.

Do you know if it is possible to export the query to a specific cell within an exisiting worksheet in Excel?

Thanks

Isskint · Jan 4, 2013

Lol, dont let something like this deter you. The code is really very simple once you break it down.

The TransferSpreadsheet action has a range parameter, so you can specify where the export should go by including the sheet name and a RANGE of cells. EG Sheet1!A1:A1 would export starting at A1 on the Sheet1 tab, ThisSheet!D12: D12 would export starting at D12 on the ThisSheet tab.

batesonm · Jan 4, 2013

Thanks again for your reply.

I will have a look at the code when I get some more time to get my head around it.

I have put in the following in the range paramater

In_Hours_Overview!E1:E1

But I get the error message saying that it already exists?

Grand Total Query (1 Viewer)

batesonm

New member

pr2-eugin

Super Moderator

Isskint

Slowly Developing

batesonm

New member

Isskint

Slowly Developing

batesonm

New member

Isskint

Slowly Developing

batesonm

New member

Similar threads

Users who are viewing this thread