Grouping Multiple Values in Queries

[DaveyCrockett]

Hello All,

I was hoping for a little knowledge on how to group multiple values together. I have a query that links multiple tables, queries, etc... and Counts multiple Serial Numbers. It groups them by the month, but I also somehow need to group them by year. As I am trying retrieve 12 months of data, count the serial number, group them by the actual month to produce a revolving report.

The revolving report will show 3 calculated values for each month starting with current month and back 11.

Does this make sense? I hope so. I am new to access and have been dropped in performing maintenance and a little modifications are required. I have searched for ways to do this, but have come up empty handed. If anyone knows any good examples and websites that will help me with this it would be greatly appreciated.

Below is the SQL from the current query:

SELECT tblProduct.Product AS [Equipment Type], Count(qryRollingMonthlySrc.WOH_ORDERNUM) AS [Total Service Request], Sum([WOH_LPRICE]+[WOH_EXPPRICE]+[WOH_TRAVPRICE]+[WOH_FLATCHRG]+[WOH_PPRICE])/Count([WOH_ORDERNUM]) AS [Average Cost], Sum([WOH_LPRICE]+[WOH_EXPPRICE]+[WOH_TRAVPRICE]+[WOH_FLATCHRG]+[WOH_PPRICE]) AS Billing, Month([WOH_COMDATE]) AS [Month], Year([WOH_COMDATE]) AS Expr1
FROM tblBusinessUnit INNER JOIN (tblProduct INNER JOIN qryRollingMonthlySrc ON tblProduct.WOH_MODELNUM = qryRollingMonthlySrc.WOH_MODELNUM) ON tblBusinessUnit.Business_Unit_ID = qryRollingMonthlySrc.WOH_CUST_DATA2
GROUP BY tblProduct.Product, Month([WOH_COMDATE]), Year([WOH_COMDATE])
ORDER BY Month([WOH_COMDATE]);


Any assistance would be greatly appreciated.

Thank you all in advance for taking the time to review my question.

 

[traingamer]

Try changing the order of your grouping:

GROUP BY Year([WOH_COMDATE]), Month([WOH_COMDATE]), tblProduct.Product

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[DaveyCrockett]

Great Thanks. Now comes the real tricky part. I am trying to create a report in Access that has the values represented in monthly sections - vertically. An example.


Serial | March | February | January etc...
Item 1 | Count AVG TTL | Count AVG TTL | Count etc...
Item 2 | Count AVG TTL | Count AVG TTL | Count etc...
Item 3 | Count AVG TTL | Count AVG TTL | Count etc...

The query above gets the current month and the 11 previous months. Is there a way to represent the recordset in a vertical fashion like this. It appears that the Reporting abilities of Access are not capable.

Thanks

 

[traingamer]

Try a crosstab query.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill