Ranking by Query

[F1stman]

Hey All,

I have a query which allows users to score projects by a number of factors
which help determine that project's priority for funding. The various scores
are totaled and the project given an overall score. I would like a query to
rank these projects according to this score and fill a field, aptly called
'Priority', according to the ranking. The higher the total score, the higher
priority. the priority values should be 1,2,3,4...etc.

Many thanks in advance. Let me know if you need more info. Adam Kemp

 

[Bob Barnes]

Have a calculated field in your Query to add those various scores.

Then sort descending on that calculated field in an Access Report.

HTH - Bob

 

[F1stman]

Hey Bob,

Thanks for responding. I need the rankings stored in the table for good. Not
just on a report. I actually need to set the value of a field based on where
the project gets ranked.

Adam Kemp

 

[Ken Sheridan]

Use a subquery to compute the Priority by counting the rows which have equal
or greater scores than the current row. For the sake of simplicity the
following example uses only two columns Score1 and Score2:

SELECT Score1 + Score2 As TotalScore,
(SELECT COUNT(*)
FROM YourTable As T2
WHERE T2.Score1 + T2.Score2 >=
T1.Score1 + T1.Score2)
AS Priority
FROM YourTable As T1
ORDER BY Score1 + Score2 DESC;

Note how the two instances of the table are distinguished by giving them
aliases T1 and T2, thus allowing the subquery to be correlated with the outer
query.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

That's not a good approach. Only the values which determine the ranking
should be stored, not values derived from them. Otherwise the table is wide
open to containing inconsistent data.

Ken Sheridan
Stafford, England