By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 853 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

Grouping Output to Excel. Help Needed!

P: 39
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....................689.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........ .........889.79
3.................600.................1035........ .........889.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........ .........889.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
Feb 7 '08 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,494
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 :
    Expand|Select|Wrap|Line Numbers
    1. SELECT [Group],
    2.        [Quantity],
    3.        [Price]
    4. FROM tblData
    5. UNION ALL SELECT [Group],
    6.                  Sum([Quantity]),
    7.                  [Price]
    8. FROM tblData
    9. GROUP BY [Group],[Price]
    10. ORDER BY [Group],[Quantity]
    Beware the total only appears at the bottom if you include the [Quantity] field in the ORDER BY clause.
Feb 7 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.