Re: Ranking formular in Access?

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
> > > >
> > > >
> > > >
.