How to break a tie of fld A using fld B

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve S - 20 Apr 2008 16:23 GMT

I use the following to calculate overall placement in a sporting event but it
produces ties that are not acceptable. the powers that be have decided to
use 'Score' to break all ties.

Rank: 1+DCount("*","tblRecapB","[Fee Id] = " & [tblRecapB].[Fee ID] & " And
AGSortKey = " & [tblRecapB].[AGSortKey] & " And MPP < " &
[tblRecapB].[Points])

Sample results of existing query are:
Score    Points    Rank
142.0    4    1
133.8    4    1
110.8    5    3
112.2    7    4

What I need is:
Score    Points    Rank
142.0    4    1
133.8    4    2
110.8    5    3
112.2    7    4

How do I do this. Can it be accomplished with a modification of existing
code or will it require an additional pass of the table?

any help is appreciated

Reply to this Message

Duane Hookom - 20 Apr 2008 17:16 GMT

I generally multiply the Points and MPP by some very large number and then
add the Score. Part of your expression would be [Points]*1000000 + Score.

I don't know what other fields are available where so I can't provide more
specifics.

Signature

Duane Hookom
Microsoft Access MVP

> I use the following to calculate overall placement in a sporting event but it
> produces ties that are not acceptable. the powers that be have decided to
[quoted text clipped - 22 lines]
>
> any help is appreciated

Reply to this Message

Steve S - 20 Apr 2008 19:00 GMT

Thank you, thank you....

I had been searching the database for 'ties' and could not believe how
difficult breaking ties seemed to be. there just had to be a simple solution
(I hoped) and you had it. Thanks again.

> I generally multiply the Points and MPP by some very large number and then
> add the Score. Part of your expression would be [Points]*1000000 + Score.
[quoted text clipped - 28 lines]
> >
> > any help is appreciated

Reply to this Message

John Spencer - 20 Apr 2008 23:00 GMT

I know you only gave us a limited sample of records, but why not just
rank by Score. That would accomplish what you want (at least with the
limited sample you posted).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Reply to this Message

Steve S - 21 Apr 2008 02:19 GMT

The history of this database is that we used to calculate overall score by
summing the scores of sub events. the problem is that one or two judges may
be a 'high scorers' while the othe one or two may be 'low scorers'.
Competitor A could have a composit score of 256.3 and competitor B a score of
254.9. B wins. However due to the variance of scoring could have had a 1st
in event X and a 3rd place in event Y whereas A had 2nd in event X and 1st
in event Y.

By using 'place' or 'rank' to determine the overall winner A now wins.
After years of discussions the official ruling is that overall winners are
determined by 'place points' and ties are broken by score. I have no choice
in the matter - I just have to come up with a solutiomn. Duane's suggestion
worked great. So simple but elegant. I think most of us got tied up in SQL
code that we could not see the solution.

> I know you only gave us a limited sample of records, but why not just
> rank by Score. That would accomplish what you want (at least with the
[quoted text clipped - 33 lines]
> >
> > any help is appreciated

Reply to this Message