RE: [MS_AccessPros] Exporting a query to MS Excel loses the totals

I believe the totals in a query is simply a display functionality. You can create a union query that combines the details and totals into a single query.


Duane Hookom MVP
MS Access

________________________________
> From: MS_Access_Professionals@yahoogroups.com
> To: MS_Access_Professionals@yahoogroups.com
> Date: Sun, 26 Apr 2015 11:53:49 -0700
> Subject: [MS_AccessPros] Exporting a query to MS Excel loses the totals
>
>
>
>
> I have about 20 queries that I'm exporting to MS Excel 2010 in this
> example. When I run the queries just in Access, I have totals for
> selected columns, selected on the home tab. When I export them, the
> totals do not get exported. I've brute forced it in the past by
> creating a temporary table with the query, then appending a totals line
> to it and exporting that table to Excel.
>
>
> An example of the VBA liine I have in the module to export is:
>
> DoCmd.TransferSpreadsheet acExport, 10, "TDREPC Summary",
> "Q:\production\isaac\Time by Date Range by Employee by Product
> Category.xlsx", True
>
>
> An example query is:
>
> SELECT [TDREPC Summary- prep].Last, [TDREPC Summary- prep].First,
> [TDREPC Summary- prep].AssociateNumber, [TDREPC Summary- prep].[Total
> min], [TDREPC Summary- prep].[Med Total], [TDREPC Summary- prep].[Bard
> Total], [TDREPC Summary- prep].[Micell Total], [TDREPC Summary-
> prep].[SII Total], [TDREPC Summary- prep].[STI Other], [TDREPC Summary-
> prep].Warehouse, [TDREPC Summary- prep].[Training Total], [TDREPC
> Summary- prep].[Overhead Total], [TDREPC Summary- prep].[Total
> Production], [TDREPC Summary- prep].Payroll, [TDREPC Summary-
> prep].[Total Factory], [TDREPC Summary- prep].Quality, [TDREPC Summary-
> prep].Total, [TDREPC Summary- prep].Delta
>
> FROM [TDREPC Summary- prep]
>
> ORDER BY [TDREPC Summary- prep].[Med Total] DESC , [TDREPC Summary-
> prep].[Bard Total] DESC , [TDREPC Summary- prep].[Micell Total] DESC ,
> [TDREPC Summary- prep].[SII Total] DESC , [TDREPC Summary- prep].[STI
> Other] DESC , [TDREPC Summary- prep].Warehouse DESC , [TDREPC Summary-
> prep].[Training Total] DESC , [TDREPC Summary- prep].[Overhead Total]
> DESC;
>
>
>
> MS Access 2010, SP2 MSO, 64 bit
>
>
>
> What am I missing for a more elegant solution? Thank you in advance
> for any suggestions.
>
>
> Buck Crouch
>
>
>
>

------------------------------------

------------------------------------


------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/