[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.....