Relative Placement

[yoshi88]

Hi, I am trying to figure out how to do this query/VBA code in Access.

I have a table name tblResults with those fields:
fldCompetitionID
fldJudgeID
fldCompetitorID
fldResult

The results are ordinal (rank 1st, 2nd, 3rd,...) with no tie allowed

Here how the relative placement works:
4. A couple must have a majority of Judges' votes to be awarded a final placement.

5. If no couple has a majority of votes, then the next placement is added to the previous placements (1st through 2nd, 1st through 3rd, etc.) until a majority is reached.

6. If two or more couples have an equal majority, then the numerical value of the ordinals for each couple is added. The couple with the lowest sum gets the higher position. If the sums for two or more couples are identical, then the next placement is added to the previous placements for those tied couples only.

7. The Chief Judge's scores are used only to break any remaining ties.





D. Example of Relative Placement:

There is an example on this page

http://www.swingdancecouncil.com/library/relativeplacement.htm

1. In the example above, there are 6 couples competing. Because 5 judges are providing placements, a majority of at least 3 Judges is needed to award any couple a final placement

2. Here, no couple has a majority of 1st place votes from the Judging panel. Adding the next placement and counting 1st through 2nd place votes, only couple #1 has a majority of at least 3 votes. Therefore, couple #1 is awarded 1st place.

3. Counting 1st through 3rd place votes, only couple #6 has a majority of at least 3 votes. Therefore, couple #6 is awarded 2nd place.

4. Counting 1st through 4th place votes, three couples have an equal majority of 3 votes. When the numerical value of the ordinals making up those votes is added, couples #2 and #3 both have the lower sum of 7, whereas couple #5 has the higher sum of 9. Couple #5 is therefore awarded the lowest final placement of the three couples, in this case 5th place.

5. To break the tie between couples #2 and #3, it is necessary to count 1st through 5th place votes, Couple #3 has 5 votes, one more than couple #2. Couple #3 therefore is awarded 3rd place, and couple #2 is awarded 4th place.

6. Couple #4, the only remaining couple, is awarded 6th place.



How can I make it work ?


Thanks



Frank

 

[markphsd]

You are asking more for a job then just a forum question. I.e. if you want the answer to this question you'd have to pay someone.

You might spend time breaking out the logic of the relative placement and then piecing it together in vba or sql. Piecing it back together is what this forum will help you do.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.