473,320 Members | 1,810 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Ranking records in a query

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 10870
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
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
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
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
>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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Shaiguy | last post by:
I have a table containing the following fields: ProjectUpdateID (PrimaryKey) ProjectID UpdateDate I would like to create a Query in Ms Access 2000 which will return me the most recent 2...
2
by: jh8735 | last post by:
I use Access to run reports from various pieces of financial data. The one query that I have been unable to produce, but need is the following: A query that yields all accounts from a person...
2
by: Jason | last post by:
I have a table of 650,000 records associated to contracts. Some of the contracts have multiple records but each contract only has one Active Record (there might be several inactive records). ...
1
by: John M | last post by:
Hi, I have two incomplete lists of staff which combined create a full list of staff with duplicates. I wish to create a full list with no duplicates. Each member of staff has and obviously a...
4
by: alltechsolutions.net | last post by:
Been at this for two days now... I have a customer database with various fields, two of which are: ContactID (an autonumber field) & ReferralID (long Integer) When a customer refers someone...
11
by: cgrider | last post by:
I have a query that checks for duplicate records on 2 fields. the issue I am having is that the data unfortunately is case sensitive. So M != m but the query is case insensitive. Is there a way to...
1
by: yfangl09 | last post by:
I have one query with a list of people and required courses they have to take and another with the same people and courses they have already taken. How do I generate a query with required courses...
3
by: sukatoa | last post by:
For example: Cat Dog Cow Rat Cat Cat Rat Cow
0
by: AdamT | last post by:
Hi I'm quite new to Access, I can do queries and only a very little bit of code. So please treat me gently. I have a query with the following fields: Area, Engineer and PC. The PC is a percentage...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.