M
Mandy
I have an Access 2000 database with a table that contains the results for
several athletics events. The competitors receive 100 points for winning an
event and for every minute behind the winning time they lose a point. They
run different courses, which are colour coded. The competitors are ranked on
their top five events, so they cannot have more than 500 points.
I cannot find a way to extract a summary of the data for each course and
competitor, only summing the top five events (TopN does not work correctly
when you have values that are the same).
This is typical data and the expected summary:
Name Course Event Points
Anne White 1 100
Anne White 2 95
Anne White 4 98
Anne White 5 100
Bob Yellow 2 100
Bob Yellow 3 95
Bob Yellow 5 100
Bob Yellow 7 92
Chris White 1 98
Chris White 2 100
Chris White 3 100
Chris White 6 92
Diane Yellow 1 100
Diane Yellow 2 97
Diane Yellow 3 100
Diane Yellow 4 100
Diane Yellow 5 96
Diane Yellow 6 98
Diane Yellow 7 97
Summary
Name Course MaxPoints
Anne White 393
Chris White 390
Diane Yellow 495
Bob Yellow 387
As you can see Diane has competed in seven events but her MaxPoints are 495
not 688.
What SQL query or stored procedure will give me the correct results?
Kind Regards,
Mandy.
several athletics events. The competitors receive 100 points for winning an
event and for every minute behind the winning time they lose a point. They
run different courses, which are colour coded. The competitors are ranked on
their top five events, so they cannot have more than 500 points.
I cannot find a way to extract a summary of the data for each course and
competitor, only summing the top five events (TopN does not work correctly
when you have values that are the same).
This is typical data and the expected summary:
Name Course Event Points
Anne White 1 100
Anne White 2 95
Anne White 4 98
Anne White 5 100
Bob Yellow 2 100
Bob Yellow 3 95
Bob Yellow 5 100
Bob Yellow 7 92
Chris White 1 98
Chris White 2 100
Chris White 3 100
Chris White 6 92
Diane Yellow 1 100
Diane Yellow 2 97
Diane Yellow 3 100
Diane Yellow 4 100
Diane Yellow 5 96
Diane Yellow 6 98
Diane Yellow 7 97
Summary
Name Course MaxPoints
Anne White 393
Chris White 390
Diane Yellow 495
Bob Yellow 387
As you can see Diane has competed in seven events but her MaxPoints are 495
not 688.
What SQL query or stored procedure will give me the correct results?
Kind Regards,
Mandy.