Ranking by Query

F

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
 
B

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
 
F

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
 
K

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
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top