A common request I get is for ranking of sales in Access by region, state and item combination for analysis and space management. In my business, we have some overlap, where a state can be part of two separate areas or regions, for example Southern CA vs. Northern CA. It’s helpful for us to show separate rankings for each region and state combo, as the results often tell a different story for each.
The Pass-Through query below enables me to determine the ranking at each of these levels. By entering as a Pass-Through query, you can utilize the Rank and Partition SQL commands. After running this query, I bring the data into Excel for further analysis, but this query is a good starting point for any similar analyses.
Here is the full Access pass-through query, color coded for the explanation below:
Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank
FROM (Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank
FROM (Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Store_monthly_sales m, FR_Site_Master s
WHERE m.Site_num = s.Site_num AND m.accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)
WHERE SumSaleQ >5)
WHERE rank <205
Order by State ASC, Region Asc, Rank Asc
Explanation
Query 1
Diving right in, since we are measuring at multiple levels, we have some nested queries. Working from the inside out, my first nested query is this one:
(Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Franchise.Store_monthly_sales m,Franchise.FR_Site_Master s
WHERE m.Site_num = s.Site_num AND accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)
Query 2
We then nest that within this query (the WHERE clause is optional – I am dealing with sales in the thousands, and want to exclude items with a sales quantity less than 5. Note the Rank() over and Partition By Commands here:
(Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank
FROM …
WHERE SumSaleQ >5)
Query 3
The final nesting and main query is selecting the rank from the second query, along with the other pertinent attributes. The WHERE clause is again optional, I am looking to limit my results to the top 200 results only for each region and state combination. I use 205 as the figure to allow for ties. The Order by is also optional. I like to order the results by state, region and then rank ascending.
Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank
FROM …
WHERE rank <205
Order by State ASC, Region Asc, Rank Asc
Full Query (no color coding)
Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank
FROM (Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank
FROM (Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Store_monthly_sales m, FR_Site_Master s
WHERE m.Site_num = s.Site_num AND m.accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)
WHERE SumSaleQ >5)
WHERE rank <205
Order by State ASC, Region Asc, Rank Asc
Conclusion
I hope this post will help you in your efforts to rank in Access. It can be a bit of a challenge, but hopefully the breakdown of the steps here will help you achieve your ranking goals. Please comment any questions, feedback, or what you would like to see next! Share this post if you feel it would help someone else! 🙂
If I helped you, please consider buying me a coffee via PayPal! Thanks!!
