Grouping Output to Excel. Help Needed!

[JC2710]

Hi

I have a problem which I cant quite solve.

I have a query that groups records together to get a Total Price.....

Group....... Total Quantity....... .....Price
1............ .....450....... .............68 9.29305
2............ .....300....... ............522 .6018875
3............ .....1035...... ...........889. 7918
4............ .....750....... ...........799. 4961375


I then have another query that shows individual quantities

Group....... Quantity Total Quantity....... .....Price
1.............. ...50.......... .......450 ............... ..689.29
1.............. ...75.......... .......450 ............... ..689.29
1.............. ...100......... ........450 ............... ..689.29
1.............. ...100......... ........450 ............... ..689.29
2.............. ...150......... ........300 ............... ..522.60
2.............. ...150......... ........300 ............... ..522.60
3.............. ...435......... ........1035... ..............8 89.79
3.............. ...600......... ........1035... ..............8 89.79
4.............. ...750......... ........750 ............... ..799.50



But i want my table to look like this (see below) without the duplication of Total Quantity and Price

Group....... Quantity Total Quantity....... .....Price
1.............. ...50.......... ............... ............... ..689.29
1.............. ...75.......... ............... ............... ...689.29
1.............. ...100......... .............. ............... ..689.29
1.............. ...100......... ........450 ............... ..689.29
2.............. ...150......... ............... ............... ..522.60
2.............. ...150......... ........300 ............... ..522.60
3.............. ...435......... ............... ............... ..889.79
3.............. ...600......... ........1035... ..............8 89.79
4.............. ...750......... ........750 ............... ..799.50


I then want to export it to excel.

I can do it in a report by adding Grouping levels but it doesnt look good when exported to excel.

Any ideas on how i can do this?

Thanks

[NeoPa]

I think your table design is a little suspect - as we need to assume that the price is the same across various records of the same group. Not normalised (Normalisation and Table structures).

However, overlooking that for now, there are two possible approaches I can see :

1. Recommended - Export the basic data into Excel and let Excel handle totalling (It's inbuilt - Data / Subtotals ...).
2. Something like the following :
   

   SELECT [Group],
          [Quantity],
          [Price]
   FROM tblData
   UNION ALL SELECT [Group],
                    Sum([Quantity]),
                    [Price]
   FROM tblData
   GROUP BY [Group],[Price]
   ORDER BY [Group],[Quantity]

   Beware the total only appears at the bottom if you include the [Quantity] field in the ORDER BY clause.