Rank Records by Product Group AND Month in MS Access
Hi Experts,
I could truly use your expertise here. I think i have been trying to find a "clear" solution for my issue for the past two months and finally decided to chek out this site to get some help with it.
I am using MS Access to create a summary report that I link to an Excel Management Dashboard. I need to show a ranking for ALL Customers, by Product Group, and Month and it has been giving me fits. So please help. I am a novice access query user and I don't know code that well so I pretty much use the query grid. I do know how to view the SQL code and make slight modifications; however I have not been successful with this.
Table Name (CY Data)
Fields:
Customer No
Product Group
Product Name
Month
Net Revenue
#Orders
I need to Rank:
Customers Total NetR evenue, by PERIOD(Month) and Product Group (total of all Products in this group)
Customers Total NetR evenue, by PERIOD(Month) and Product
ProductGroup Total Net Revenue and Month
ProductGroup PRODUCT total Revenue and Month
I would be truly grateful to get an expert to help me with this - it is causing me heart palpatations! Truly.
Thanks,99TPenn
I could truly use your expertise here. I think i have been trying to find a "clear" solution for my issue for the past two months and finally decided to chek out this site to get some help with it.
I am using MS Access to create a summary report that I link to an Excel Management Dashboard. I need to show a ranking for ALL Customers, by Product Group, and Month and it has been giving me fits. So please help. I am a novice access query user and I don't know code that well so I pretty much use the query grid. I do know how to view the SQL code and make slight modifications; however I have not been successful with this.
Table Name (CY Data)
Fields:
Customer No
Product Group
Product Name
Month
Net Revenue
#Orders
I need to Rank:
Customers Total NetR evenue, by PERIOD(Month) and Product Group (total of all Products in this group)
Customers Total NetR evenue, by PERIOD(Month) and Product
ProductGroup Total Net Revenue and Month
ProductGroup PRODUCT total Revenue and Month
I would be truly grateful to get an expert to help me with this - it is causing me heart palpatations! Truly.
Thanks,99TPenn
Hi,
Can you post some sample data or even a sample DB to work from. This seems to be more than one query required here. I would suggest a crosstab query "may" work in this situation.
Kev
Can you post some sample data or even a sample DB to work from. This seems to be more than one query required here. I would suggest a crosstab query "may" work in this situation.
Kev
For your first query, try this and let me know how it goes. Once we've got that sorted we can work on the others.
Note: You will need to copy and paste this into the query SQL view.
(I have assumed your month field is an integer from 1 to 12, also I have assumed the period is a financial period from July to June).
SELECT [Customer No], Sum([Net Revenue]) AS [Total Net Revenue], Month(DateAdd("m",6,"1 " & [Month] & ", 2009")) AS Period, [Product Group]
FROM [CY Data]
GROUP BY [Customer No], Month(DateAdd("m",6,"1 " & [Month] & ", 2009")), [Product Group]
ORDER BY Month(DateAdd("m",6,"1 " & [Month] & ", 2009")), [Product Group], Sum([Net Revenue]) DESC
Note: You will need to copy and paste this into the query SQL view.
(I have assumed your month field is an integer from 1 to 12, also I have assumed the period is a financial period from July to June).
SELECT [Customer No], Sum([Net Revenue]) AS [Total Net Revenue], Month(DateAdd("m",6,"1 " & [Month] & ", 2009")) AS Period, [Product Group]
FROM [CY Data]
GROUP BY [Customer No], Month(DateAdd("m",6,"1 " & [Month] & ", 2009")), [Product Group]
ORDER BY Month(DateAdd("m",6,"1 " & [Month] & ", 2009")), [Product Group], Sum([Net Revenue]) DESC
ASKER
The MONTH is a number 1 - 12. As for the Year I do compare current and Prior Year; however I just make two sets of data sets and compare the two years.
ASKER
Here is some sample data of the table used. This is summarized data and I used this table to build all my other analysis tables from.
I rank Revenue, Pcs, and Wgt totals by month, per customer, product and product group
querydatasample.xls
I rank Revenue, Pcs, and Wgt totals by month, per customer, product and product group
querydatasample.xls
ASKER
TramTrak,
I tried your SQL code and modified to this:
SELECT Customer AS CustID,[Customer Name],Product AS ProductID,Sum([Total Net Revenue]) AS [TotalRev], [Reporting Group],[Period]
FROM [Step 1| CY XB Customer Volumes]
GROUP BY [Customer],[Customer Name],[Period], [Reporting Group],[product]
ORDER BY [Period], [Reporting Group], Sum([Total Net Revenue]) DESC
The result is that the data is sorted by Period, Product Group, and Highest revenue. This is correct sort. Now just need to have Rank for revenue in the Period/Product Group
I tried your SQL code and modified to this:
SELECT Customer AS CustID,[Customer Name],Product AS ProductID,Sum([Total Net Revenue]) AS [TotalRev], [Reporting Group],[Period]
FROM [Step 1| CY XB Customer Volumes]
GROUP BY [Customer],[Customer Name],[Period], [Reporting Group],[product]
ORDER BY [Period], [Reporting Group], Sum([Total Net Revenue]) DESC
The result is that the data is sorted by Period, Product Group, and Highest revenue. This is correct sort. Now just need to have Rank for revenue in the Period/Product Group
When you insert a month and leave out the date you are doing something Access is very good at and leave a lot of questions. Assuming your transaction date is named TDate:
SELECT "Customer" AS Source, CustomerID, Format(TDate,"yymm") AS YYMM, Sum([Net Revenue]) as [Total Revenue] FROM [CY Data] GROUP BY "Customer", CustomerID, Format(TDate,"yymm")
UNION
SELECT "Product", [Product Name], Format(TDate,"yymm"), Sum([Net Revenue]) FROM [CY Data]
GROUP BY "Product", [Product Name], Format("yymm")
UNION "Product Group", [Product Group], Format(TDate,"yymm"), Sum([Net Revenue]) FROM [CY Data]
GROUP BY "Product Group", [Product Group], Format(TDate,"yymm")
ORDER BY 1,2,3,4;
SELECT "Customer" AS Source, CustomerID, Format(TDate,"yymm") AS YYMM, Sum([Net Revenue]) as [Total Revenue] FROM [CY Data] GROUP BY "Customer", CustomerID, Format(TDate,"yymm")
UNION
SELECT "Product", [Product Name], Format(TDate,"yymm"), Sum([Net Revenue]) FROM [CY Data]
GROUP BY "Product", [Product Name], Format("yymm")
UNION "Product Group", [Product Group], Format(TDate,"yymm"), Sum([Net Revenue]) FROM [CY Data]
GROUP BY "Product Group", [Product Group], Format(TDate,"yymm")
ORDER BY 1,2,3,4;
ASKER
GaryL,
That code is not working for me- I am getting a INVALID SQL STATEMENT argument. I do not have a TDate (transaction date). I have two seperate tables that distinguish CURRENT YEAR and PRIOR YEAR data. So each table has a Month and Year Field.
More importantly where is the RANK Field? That is ultimately what I need here. A rank of
Customer by PRODUCT/Period/Revenue
Once i have this I can figure out how to group the others.
Thanks
That code is not working for me- I am getting a INVALID SQL STATEMENT argument. I do not have a TDate (transaction date). I have two seperate tables that distinguish CURRENT YEAR and PRIOR YEAR data. So each table has a Month and Year Field.
More importantly where is the RANK Field? That is ultimately what I need here. A rank of
Customer by PRODUCT/Period/Revenue
Once i have this I can figure out how to group the others.
Thanks
Hi Penndro,
Please explain what you mean by "Rank" for revenue.
I am confused because you have stated that the items are already sorted by highest revenue.
Cheers.
Please explain what you mean by "Rank" for revenue.
I am confused because you have stated that the items are already sorted by highest revenue.
Cheers.
ASKER
Yes, but i need a NUMBER rank next to each record. So even though it is sorted i need to show a rank or say a Row ID which would start at 1 and count the records to the end of the group.
The data table summarizes fine however i need the rank number to do my reporting. When all the product groups are in one table then the sort DESC is useless becasue i can determine when the group count starts and ends.
The data table summarizes fine however i need the rank number to do my reporting. When all the product groups are in one table then the sort DESC is useless becasue i can determine when the group count starts and ends.
Hi Penndro,
It sounds like you're trying to over-complicate your table.
A table should only be for storing details, a good rule of thumb is 'anything that can be calculated at runtime, should be'.
How do you want to report your data? Perhaps that is where you should be looking at inserting the rank number.
That will also mean you do not need to update your tables everytime you run your reports just to recalculate the current rank numbers.
It sounds like you're trying to over-complicate your table.
A table should only be for storing details, a good rule of thumb is 'anything that can be calculated at runtime, should be'.
How do you want to report your data? Perhaps that is where you should be looking at inserting the rank number.
That will also mean you do not need to update your tables everytime you run your reports just to recalculate the current rank numbers.
ASKER
i am exporting data to Excel to use in a Dashboard. I pull in the appropriate records by vlookup of the Rank number.
So yes, the query summarizes the data - now i need to apply a rank because in Excel I know of SUMIF but not RANKIF
So yes, the query summarizes the data - now i need to apply a rank because in Excel I know of SUMIF but not RANKIF
Penndro,
I would suggest perhaps using a macro in Excel to add the Rank numbers?
If you really want to do it with SQL though, try this:
(I have adjusted the table fields to match your query above)
SELECT CustID,[Customer Name],ProductID,[TotalRev]
SELECT Count([TotalRev])+1
FROM (
SELECT Customer,[Customer Name],Product AS ProductID,Sum([Total Net Revenue]) AS [TotalRev], [Reporting Group],[Period]
FROM [Step 1| CY XB Customer Volumes]
GROUP BY [Customer],[Customer Name],[Period], [Reporting Group],[product]
) AS q2
WHERE q2.TotalRev > q1.TotalRev AND q2.Period = q1.Period AND q1.[Reporting Group] = q2.[Reporting Group]
) AS Rank
FROM (
SELECT Customer AS CustID,[Customer Name],Product AS ProductID,Sum([Total Net Revenue]) AS [TotalRev], [Reporting Group],[Period]
FROM [Step 1| CY XB Customer Volumes]
GROUP BY [Customer],[Customer Name],[Period], [Reporting Group],[product]
) AS q1
GROUP BY CustID,[Customer Name],ProductID,[TotalRev]
ORDER BY [Period], [Reporting Group],[TotalRev] DESC
I would suggest perhaps using a macro in Excel to add the Rank numbers?
If you really want to do it with SQL though, try this:
(I have adjusted the table fields to match your query above)
SELECT CustID,[Customer Name],ProductID,[TotalRev]
SELECT Count([TotalRev])+1
FROM (
SELECT Customer,[Customer Name],Product AS ProductID,Sum([Total Net Revenue]) AS [TotalRev], [Reporting Group],[Period]
FROM [Step 1| CY XB Customer Volumes]
GROUP BY [Customer],[Customer Name],[Period], [Reporting Group],[product]
) AS q2
WHERE q2.TotalRev > q1.TotalRev AND q2.Period = q1.Period AND q1.[Reporting Group] = q2.[Reporting Group]
) AS Rank
FROM (
SELECT Customer AS CustID,[Customer Name],Product AS ProductID,Sum([Total Net Revenue]) AS [TotalRev], [Reporting Group],[Period]
FROM [Step 1| CY XB Customer Volumes]
GROUP BY [Customer],[Customer Name],[Period], [Reporting Group],[product]
) AS q1
GROUP BY CustID,[Customer Name],ProductID,[TotalRev]
ORDER BY [Period], [Reporting Group],[TotalRev] DESC
ASKER
TramTrak,
Can you explain how I would rank the following in Excel with a macro?
Period | Product Group | Product Name | Revenue | CustomerID
The sort is from left to right.
Can you explain how I would rank the following in Excel with a macro?
Period | Product Group | Product Name | Revenue | CustomerID
The sort is from left to right.
ASKER
TramTrack,
Thanks for the help -- looks like the SQL code you gave above works like a charm. I am testing it out. A little slow but looks so far very effective. Will let you know soon.
Thanks again,
Penndro
Thanks for the help -- looks like the SQL code you gave above works like a charm. I am testing it out. A little slow but looks so far very effective. Will let you know soon.
Thanks again,
Penndro
You should be doing this with your raw data - before any sums, month, quarter, year separations. As to ranking, you can get incremental values like that easily in forms and reports.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Superb work! I had about 15 queries to try and rank and with your solution I am able to streamline my summary report. I appreciate it so much as you have save me a ton of grief.
Is the month field just a number from 1 to 12? i.e. No value for the year?