[AccessD] Subtotals to Excel

Paul,

Another option:  You could do a little Excel automation on the file.  I
recorded a simple subtotal macro that you could run after your data has been
exported into the correct fields.  Results are shown below.


Mark


Sub AutoSubtotals()
    Selection.Subtotal GroupBy:=2, _
                      Function:=xlSum, _
                     TotalList:=Array(3), _
                       Replace:=True, _
                    PageBreaks:=False, _
              SummaryBelowData:=True
End Sub


Date    	Shift		Quantity
8/26/2003	1		34
8/26/2003	1		32
    		1 	Total	66
8/26/2003	2		12
    		2 	Total	12
8/26/2003	3		56
8/26/2003	3		43
		3	Total	99
		Grand	Total177



-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk] 
Sent: Wednesday, August 27, 2003 5:46 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Subtotals to Excel


Hi paul

You can use a union query for this.

Create a select query with the details and one more field, Subtotal, which
you set to False as an expression. Create a summing query where you Group By
Shift, Sum Quantity and add one more field, Subtotal, which you set to True
as an expression. You may need a date field as well as you need to pull the
same number of fields from both queries; set this to Date() as an
expression.

Union these two in a union query where you order by Subtotal and Date.
Remember to state UNION ALL ..

Or you could use IBM's DB2 which can create this in one go ...

/gustav


> I have a access query which will contain a range of dates and a shift 
> number (1,2,3), which will normally have data similar to the
> following:

> Date            Shift            Quantity

> 26/08/03         1                 34
> 26/08/03         1                 32
> 26/08/03         2                 12
> 26/08/03         3                 56
> 26/08/03         3                 43

> I need to export this query out to Excel but stop after each shift 
> number finishes and add sub-totals into the Excel sheet like so :

> Date            Shift            Quantity

> 26/08/03         1                 34
> 26/08/03         1                 32
>                   Sub-Total Qty   66
> 26/08/03         2                 12
>                   Sub-Total Qty   12
> 26/08/03         3                 56
> 26/08/03         3                 43
>                    Sub-Total Qty   99

> Anyone know a way that this can be achieved........
> Thanks in advance for any help.....

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com