Ranking formular in Access?

[Paul (ESI)]

I use Access 2002, my colleague uses 2003. We are creating a spreadsheet and
corresponding database. I've been talking about it in several of my recent
posts. I asked a question about ranking in an Excell spreadsheet and got a
lot of great help. Thanks again, guys.

However, now we are considering doing the ranking in Access instead. So, for
example, we'd want to list each supervisor, the average attendance score for
their employees, their average schedulle adherence score for their employees,
the average quality score for their employees, and the average of the handle
time for their employees. Then, each weighted score should be calculated
based on what weight we gave each category. Then, a total score should be
calculated for each supervisor.

Then, each supervisor should be ranked in each of those fields (each
weighted score, and the overall total), based on each other. In other words,
if supervisor Meep has total score of 100%, supervisor Slimer has 98%,
supervisor Batman has 95%, supervisor Superman has 96%, the database should
go to each row and attach the appropriate rank to another field. In other
words, Meep ranked 1, Slimer ranked 2, Batman ranked 4, Superman ranked 3.
Can this be done in a query, or can it even be done in the table itself? I
figured, if it can be done, it probably has to be in a query, so I posted it
here. I would be just fine, however, with it being in the table itself. Can
this be done? Any help would be greatly appreciated.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

 

[Paul (ESI)]

Dagnabbit! I need more sleep. "Ranking formular?!" I swear to you I did not
mean to type that.
--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

 

[Michel Walsh]

Hi,



SELECT a.f1, COUNT(*) As rank
FROM myTable As a INNER JOIN myTable As b
ON a.f2 <= b.f2
GROUP BY a.f1



will rank each f1 (people name) accordingly to their f2 value ( % rate) .


Ex-equo will be 'late'. As example, with 100, 98, 98 96, ... the ranking
will be 1, 3, 3, .4 ..


Another alternative is to use a subquery:

SELECT a.f1, (SELECT COUNT(*) FROM myTable As b WHERE a.f2<=b.f2) As Rank
FROM myTable As a







Hoping it may help,
Vanderghast, Access MVP

 

[Paul (ESI)]

Awesome, thanks! I couldn't get your first suggestion to work, but I got the
subquery to work just fine. Thanks!

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

 

['69 Camaro]

Hi, Paul.

To add some information to Michel's suggestion, you may not like the way
Access ranks ties. For example, if two people were tied for first place,
then the next person listed would be ranked as third, not second. Please see
example 3 on the following Web page for one method of ranking ties the same
way Excel does:

http://support.microsoft.com/default.aspx?id=208946

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

['69 Camaro]

Oops! Correction: With the example with the subquery that Michel suggested,
the two people tied for first place would both be ranked at #2 in my example
below. The person in third place would be ranked at #3, as he should be.
However, most people would take a dislike to being declared the winner, yet
ranked at #2.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[Paul (ESI)]

Cool. Thanks for the additional help.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

 

[Paul (ESI)]

Oh wow! I just noticed something really weird. Your netname here is '69
Camaro, and your profile now says you've helped 69 users! Groovy!

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

 

['69 Camaro]

Since I've met my allotted quota, I guess that means I'll have to stop
finding new people to help, because I'm not trading my favorite car in on a
newer model! ;-)

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

P.S. Thanks for marking my response as a correct answer to your question.
It's much appreciated!

 

[Paul (ESI)]

He he he. Yeah, that, or you could threaten bodily harm to anybody who rates
you helpful. ;-)

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

 

[Michel Walsh]

Hi,


In theory, we can also get the ranking of 10, 10, 30, 40 as being 1, 1,
3, 4 with:

SELECT a.f1, 1+ COUNT(b.f1)
FROM myTable As a LEFT JOIN myTable As b
ON a.f2 < b.f2
GROUP BY a.f1



while to get 2, 2, 3, 4, the previous solution, with an inner join, a <=
comparison and a COUNT(*) rather than 1+COUNT(b.fieldName) should do.




Hoping it may help,
Vanderghast, Access MVP