How to break a tie of fld A using fld B

[Steve S]

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

 

[Duane Hookom]

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.

 

[Steve S]

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.

I don't know what other fields are available where so I can't provide more
specifics.
--
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
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

 

[John Spencer]

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
'====================================================

 

[Steve S]

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
limited sample you posted).

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

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