Rank Records by Product Group AND Month in MS Access

Penndro
Penndro used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ioanePlanning & Analytics Manager

Commented:
Hi Penndro,

Is the month field just a number from 1 to 12? i.e. No value for the year?
Kev

Commented:
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
ioanePlanning & Analytics Manager

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.

Author

Commented:
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

Author

Commented:
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

Commented:
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;

Author

Commented:
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
ioanePlanning & Analytics Manager

Commented:
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.

Author

Commented:
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.
ioanePlanning & Analytics Manager

Commented:
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.

Author

Commented:
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

ioanePlanning & Analytics Manager

Commented:
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], [Reporting Group],[Period], (
    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], [Reporting Group],[Period]
ORDER BY [Period], [Reporting Group],[TotalRev] DESC

Author

Commented:
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.  

Author

Commented:
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

Commented:
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.
Planning & Analytics Manager
Commented:
Hi Penndro,

The SQL I gave you is really not the best way to do it, although it works.

If you decide you want to use a macro in Excel, you should raise it as a new question.

If you're happy with the SQL version then, glad I could help.

I have attached a very slightly more efficient version here:
SELECT CustID,[Customer Name],ProductID,[TotalRev], [Reporting Group],[Period], (
    SELECT Count([TotalRev])
    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], [Reporting Group],[Period]
ORDER BY [Period], [Reporting Group],[TotalRev] DESC

Open in new window

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial