Combine monthly product sales query

[tl mike]

I was wondering if there is a way to combine sales by month for a year where it would show the product then for say January and the total sales and so on for each month.

 

[pbaldy]

I'm guessing a totals query that grouped on product and month and summed sales would work.

 

[tl mike]

Thanks for the quick reply which I had finally figured it out but now my new problem is I want to show this in a rpt with partnumber being the sort group but I need the months and totals for those months going horizontal instead of vertical. Is there a way of doing this??

 

[tl mike]

It would look something like this

PartNumber 1
Month 1 Month 2 Month 3
Total 1 Total 2 Total 3

PartNumber 2
Month 1 Month 2 Month 3
Total 1 Total 2 Total 3

Sql for the qry is
SELECT tblPartNumber.PartNumber, Sum(tblProductUsageDetails.Quantity) AS Total, DatePart("m",[Date]) AS [Month]
FROM tblPartNumber INNER JOIN tblProductUsageDetails ON tblPartNumber.PartNumberID = tblProductUsageDetails.ProductFK
WHERE (((tblProductUsageDetails.Date) Between #1/1/2007# And #12/31/2007#))
GROUP BY tblPartNumber.PartNumber, DatePart("m",[Date])
ORDER BY tblPartNumber.PartNumber, DatePart("m",[Date]);

 

[pbaldy]

Play with the crosstab query wizard, which is the tool usually used to display data that way.

 

[tl mike]

Will do

Thanks

 

[tl mike]

It worked perfectly !!!!!

Thank you for the help

 

[pbaldy]

No problem; glad we got it sorted out.