Re: Ranking formular in Access?

Tech-Archive recommends: Speed Up your PC by fixing your registry

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)" <PaulESI@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C0ECE8D7-3219-47F3-A6E7-11290665855F@xxxxxxxxxxxxxxxx
>I use Access 2002, my colleague uses 2003. We are creating a spread***
>and
> corresponding database. I've been talking about it in several of my recent
> posts. I asked a question about ranking in an Excell spread*** 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


.

Quantcast