Re: Ranking formular in Access?

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)" <PaulESI@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FB9BEF3B-7991-4BEF-989E-3978AEB1E1E5@xxxxxxxxxxxxxxxx
> 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
>
>
> "Paul (ESI)" wrote:
>
>> Cool. Thanks for the additional help.
>>
>> --
>> Have a nice day!
>>
>> ~Paul
>> Express Scripts,
>> Charting the future of pharmacy
>>
>>
>> "'69 Camaro" wrote:
>>
>> > 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 Ripley@xxxxxxxxxxxxxxx and scott@xxxxxxxxxxxxxxxxxx
>> >
>> > - - -
>> > 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" wrote:
>> >
>> > > 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 Ripley@xxxxxxxxxxxxxxx and scott@xxxxxxxxxxxxxxxxxx
>> > >
>> > > - - -
>> > > 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)" wrote:
>> > >
>> > > > 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
>> > > >
>> > > >
>> > > > "Michel Walsh" wrote:
>> > > >
>> > > > > 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
>> > > > >
>> > > > >
>> > > > >


.

Loading