summarising calculated data retrieved in a query

[tonygg]

Hi. Thank you in advance for looking at my problem. I am not exactly sure what to call it so I am showing the sql and results to explain.

I have a query that calculates the cost of stock and its retail value for each product in my DB (using a LEFT JOIN between 2 tables and some simple calculations based on stock * price):

SELECT TONYProductHistoryTable.[Product Reference Num], TONYProductHistoryTable.[Product Description], TONYProductHistoryTable.[Product Section Name], TONYProductHistoryTable.[Product Price Type], TONYProductHistoryTable.[Cost Price of Product], TONYProductHistoryTable.[Retail Price Product], TONYProductHistoryTable.[Retail Price Permutation], Product.nStockOnHand, Product.[Product Reference], [TONYProductHistoryTable].[Cost Price of Product]*[Product].[nStockOnHand] AS ["Cost of Stock"], Iif(TONYProductHistoryTable.[Retail Price Product] >0, round((([TONYProductHistoryTable].[Retail Price Product]*[Product].[nStockOnHand])/1.2)*.95,2), round((([TONYProductHistoryTable].[Retail Price Permutation]*[Product].[nStockOnHand])/1.2)*.95,2) ) AS ["retail value of Stock"]
FROM TONYProductHistoryTable LEFT JOIN Product ON TONYProductHistoryTable.[Product Reference Num] = val(Product.[Product Reference])
WHERE (((Product.nStockOnHand)>0))
ORDER BY TONYProductHistoryTable.[Product Section Name], TONYProductHistoryTable.[Product Price Type]

The results are ordered by the section of the product (basically the family grouping of the product) and the variant of the product (a few different ways you can buy each product). The results are:

Product Reference Num	Product Description	Product Section Name	Product Price Type	Cost Price of Product	Retail Price Product	Retail Price Permutation	nStockOnHand	Product Reference	"Cost of Stock"	"retail value of Stock"
1333	30s Playtime Sea Bias Dotted Stripe 32785-12	30s Playtime Favourites	Moda	£1.40	£0.00	£2.75	2	1333	£2.80	4.35
1334	30s Playtime Sea Wonky Squares 32786-12	30s Playtime Favourites	Moda	£1.40	£0.00	£2.75	1	1334	£1.40	2.18
2230	30s Playtime 2014 Charm Pack	30s Playtime Favourites	Moda Pre-cuts	£4.88	£10.00	£0.00	2	2230	£9.76	15.83
3401	Ahoy Red Linen Texture 1473-R	Ahoy	Makower	£1.18	£0.00	£2.50	7	3401	£8.26	13.85
3385	Ahoy Cream Linen Texture 1473-Q	Ahoy	Makower	£1.18	£0.00	£2.50	24	3385	£28.32	47.5
3402	Ahoy Red Stripe 1471-R	Ahoy	Makower	£1.18	£0.00	£2.50	1	3402	£1.18	1.98
3647	Alisons Flowers Pink Bundle	Alisons Flowers	Bundles	£7.50	£15.00	£0.00	3	3647	£22.50	35.62
3649	Alisons Flowers Teal Bundle	Alisons Flowers	Bundles	£7.50	£15.00	£0.00	6	3649	£45.00	71.25
3648	Alisons Flowers Purple Bundle	Alisons Flowers	Bundles	£7.50	£15.00	£0.00	5	3648	£37.50	59.38
3526	Alisons Flowers Blue Flower Scroll 1432-T	Alisons Flowers	Makower	£1.18	£0.00	£2.50	13	3526	£15.34	25.73
3531	Alisons Flowers Blue Forget Me Not 1433-T	Alisons Flowers	Makower	£1.18	£0.00	£2.50	13	3531	£15.34	25.73
3532	Alisons Flowers Cream Spaced Daisy 1437-Q	Alisons Flowers	Makower	£1.18	£0.00	£2.50	60	3532	£70.80	118.75

I would really like to only see the data summed up at product section name and product price type. So in the example above instead of returning 12 rows it would instead return the 5 "summary" rows that adds up the cost of stock and the retail value of stock that make up the summary :

30s Playtime Favourites, Moda , ........ £4.20 , £6.53
30s Playtime Favourites, Moda Pre_Cuts , ....... £9.76 , 15.83
Ahoy, Makower , ....... £37.76, £63.33


Alisons Flowers, Bundles, .......... £105.00, £166.25
Alisons Flowers, Makower, ....... etc...

I would be very grateful if someone could point me in the right direction.

Many thanks

Tony

[NTC]

One would use an Aggregate Query; this is the Sigma icon in the ribbon when in query design view (looks like a big E). You can research that topic for examples to get the hang of it.

[tonygg]

Great. sorted thank you. I have the sub totals now by section and product type. I used a new query to aggregate the data from the original query. I was hoping to also get a grand total for the cost of stock and retail Price. I added a total line using the sigma icon in the datasheet view of the query. This worked in the query but when I used the query to create a form to display the results it does not show. I guess I am making a stupid mistake somewhere?

Many thanks

Tony