[AccessD] Subtotals to Excel

Or wait until the raw data gets imported into Excel, and use Excel's magical
subtotaling tool (Liz Doering showed me this yesterday).  First insert a row
above the values and add some titles. Select the whole block of titles and
data.  Then from the menu, choose Data, Subtotals.

Steve Capistrant
Symphony Information Services
scapistrant at symphonyinfo.com
Phone: 612-333-1311
www.symphonyinfo.com
212 3rd Ave N, Ste 404
Minneapolis, MN 55401


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Wednesday, August 27, 2003 4: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