By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 828 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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.