Ranking per Group in MS Access Query
Posted on 2012-09-07
I am hoping that someone would be able to provide some quick assistance on this one.
I have a MS Access database that stores all of the sales data/transactions by customer name in each market. The fields in this table are:
I also have a query that sums the volume sales for each customer called qry_Customer_Total_Sales.
I created another query that allowed me to rank each customer in the database regardless of which market they are from (i.e. across the entire business) as follows:
SELECT Ranking.Market_ID, Ranking.Customer_No, Ranking.Customer_Name, Ranking.Total, (Select Count(*) from qry_Customer_Total_Sales Where [Total] > [Ranking].[Total])+1 AS Ranking
FROM qry_Customer_Sales AS Ranking
ORDER BY Ranking.Market_ID, Ranking.Total DESC;
However, I would like to now rank each customer within their respective markets. For example, I would like to show that a customer may be ranked #20 across all customers but ranked #1 in their respective market.
I suspect that a subquery would be needed (similarly to the Top N per group query) but I am having some trouble figuring this out.
Can someone please assist?