rank numeric data in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have as series of records with calculated numeric scores from 3 to 900. I
would like access to to calculate each record's rank with respect to one
another and fill that rank into another field.
 
Dear Curran:

This can be done with a correlated subquery.

If you'll post the SQL of a query showing everything ELSE you want but the
rank, and point out the name of the column on which you want to rank, I'll
modify that to add the rank column.

Tom Ellison
 
Hi Tom:

Thanks for getting to this for me. What I have is a field of numeric scores
for a landslide hazard rating. These have been calculated from other update
queries. What I'm trying to do is create the query to assign a rank of each
record based on the hazard score. This way when I present data, I can show
how each landslide is ranked statewide on a tabular report and in a GIS
project. All of the data resides in the Primary Slide Table, so the numeric
hazard data is in PrimarySlideTable.HazScore, and I want to put it in
PrimarySlideTable.Ste_Hz_Rnk

Thanks,

Curran Mohney
 
Dear Curran:

You have given me the name of your table and one column. Here's what I can
do with that:

SELECT HazScore,
(SELECT COUNT(*)
FROM PrimarySlideTable T1
WHERE T1.HazScore < T.HazScore)
AS Rank
From PrimarySlideTable T
ORDER BY HazScore

There's no sense in putting this in a new column in the table. Every time
you add new rows, delete any row, or modify the HazScore of any row, the
ranking can change. It needs to be derived "live" whenever you need it.

It may be you want them ranked in the opposite order:

SELECT HazScore,
(SELECT COUNT(*)
FROM PrimarySlideTable T1
WHERE T1.HazScore > T.HazScore)
AS Rank
From PrimarySlideTable T
ORDER BY HazScore DESC

Tom Ellison
 
Tom:

Thank you so much for your help! I've now completed the query that ranks
all of my data the way I wanted it. I only made the following addition:

SELECT HazScore,
(SELECT COUNT(*)
FROM PrimarySlideTable T1
WHERE T1.HazScore > T.HazScore)+1
AS Rank
From PrimarySlideTable T
ORDER BY HazScore
Click to expand...

This gave my top score a rank of "1" rather than "0".

Again, thank you

Curran Mohney
 
Curran, this has helped me a lot with my similiar situation. What do I do if
I need to rank them by state within region?
 
Tom, your example for Curran helped me tremendously with my similiar problem,
however I need the ranking by state within reason. Can you help me
accomplish that?
 
Oops, region
--
Thank you for your help.


Bill said:
Tom, your example for Curran helped me tremendously with my similiar problem,
however I need the ranking by state within reason. Can you help me
accomplish that?
Click to expand...
 
Back
Top