434,578 Members | 828 Online
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

# Ranking records in a query

 P: n/a Hi, The question below was posted about 4 years ago and noone was able to answer it back then. I have virtually the same type of problem so would appreciate if anyone can help. Thanks ================================================== ==================== I have read through multiple posts on ranking records in an Access query but have not been able to located one that addresses my scenario exactly. I have a query that pulls potential customers by geographic territory and provides a total market potential for each customer shown as unit sales. The query ranks the territories in ascending order and the market potential in descending order so that I wind up with territory #1 and the highest potential customer as the first record followed by every customer in the territory is descending potential order, e.g. Territory Cust Potential (Unit Sales) 1000 200 1000 178 1000 143 .. .. 1001 225 1001 197 etc. Total record count is 377,630. I need to rank each customer based on their potential within each territory so that my query winds up with the following Territory Cust Potential (Unit Sales) Rank 1000 200 1 1000 178 2 1000 143 3 .. .. 1001 225 1 1001 197 2 Any assistance is greatly appreciated. ================================================== ==================== May 5 '06 #1
5 Replies

 P: n/a First you have to create a module then paste this code to that new module Option Compare Database Dim CustID As Integer Dim ctr As Integer Function R(Id As Integer, intVal As Integer) As Integer If CustID <> Id Then ctr = 1 CustID = Id Else ctr = ctr + 1 End If R = ctr End Function Then use this query SELECT Territory, Potential,R(Territory, Potential) as rank FROM Table1 ORDER BY Territory, Potential DESC; May 5 '06 #2

 P: n/a va*****@mail.com wrote: Hi, The question below was posted about 4 years ago and noone was able to answer it back then. I have virtually the same type of problem so would appreciate if anyone can help. Thanks ================================================== ==================== I have read through multiple posts on ranking records in an Access query but have not been able to located one that addresses my scenario exactly. I have a query that pulls potential customers by geographic territory and provides a total market potential for each customer shown as unit sales. The query ranks the territories in ascending order and the market potential in descending order so that I wind up with territory #1 and the highest potential customer as the first record followed by every customer in the territory is descending potential order, e.g. Territory Cust Potential (Unit Sales) 1000 200 1000 178 1000 143 . . 1001 225 1001 197 etc. Total record count is 377,630. I need to rank each customer based on their potential within each territory so that my query winds up with the following Territory Cust Potential (Unit Sales) Rank 1000 200 1 1000 178 2 1000 143 3 . . 1001 225 1 1001 197 2 Any assistance is greatly appreciated. ================================================== ==================== Did you search Google Groups (in this group) for "Ranking query"? There are lots of posts on this since it is a common question. The ranking is determined by counting records outside some threshold. Here is a sample: SELECT p.KeyPct , (SELECT COUNT(*) FROM tblMiscPct WHERE KeyPct >= p.KeyPct) AS Rank FROM tblMiscPct AS p ORDER BY p.KeyPct DESC; Here "KeyPct" determines the order, highest value first. The second field ("Rank") is a subquery count of records that equal or exceed the current record's value (resulting in rank). -- '--------------- 'John Mishefske '--------------- May 5 '06 #3

 P: n/a Arthur Thanks a lot! This is what I needed. One more thing - would this solution work if I had an extra field - a Subterritory so say there are field Territory Subterritory Value 1000 100 50 1000 100 40 1000 90 30 1000 90 20 1000 90 10 So, the ranking would have to be done against a Territory-Subterritory combination. Is there a solution to that? Thanks again. May 6 '06 #4

 P: n/a va*****@mail.com wrote in message <11**********************@y43g2000cwc.googlegroups .com> : Arthur Thanks a lot! This is what I needed. One more thing - would this solution work if I had an extra field - a Subterritory so say there are field Territory Subterritory Value 1000 100 50 1000 100 40 1000 90 30 1000 90 20 1000 90 10 So, the ranking would have to be done against a Territory-Subterritory combination. Is there a solution to that? Thanks again. This is going to be a drag on recourses, but here's an untested attempt at SQL. You should be able to find similar if searching for "ranking query". select (select count(*) from mytable s where s.Territory = t.Territory and s.Subterritory >= t.Subterritory and s.[Value] >= t.[Value]) as field1, t.Territory, t.Subterritory, t.[Value] from mytable t order by t.Territory, t.Subterritory DESC, t.[Value] DESC I might have mixed up a bit, if so, try changing the line and s.Subterritory >= t.Subterritory to and s.Subterritory = t.Subterritory -- Roy-Vidar May 6 '06 #5

 P: n/a >would this solution work if I had an extra field Yes, but you have to make some adjustment in the function and to the query. May 8 '06 #6

### This discussion thread is closed

Replies have been disabled for this discussion.