Hey,
I've been developing my first MS Access database for a while now and making some steady progress. I've got various tables and queries set up which are all working well, but am stuck on one particular issue.
This is an athletics scoring database, which converts an athletes individual performance into scores.
I'm using SELECT TOP 3 to return the best three scores from a list of performances by any given athlete.
Sometimes an athlete will score the same points for repeated performances - imagine your best 5 results score 700, 700, 650, 650 and 400 points. I therefore only want to total 700+700+650=2050points.
My code is
I've been developing my first MS Access database for a while now and making some steady progress. I've got various tables and queries set up which are all working well, but am stuck on one particular issue.
This is an athletics scoring database, which converts an athletes individual performance into scores.
I'm using SELECT TOP 3 to return the best three scores from a list of performances by any given athlete.
Sometimes an athlete will score the same points for repeated performances - imagine your best 5 results score 700, 700, 650, 650 and 400 points. I therefore only want to total 700+700+650=2050points.
My code is
Code:
SELECT t.AthleteID, t.AthleteName, t.Gender, t.AgeGroup, t.ClubID, t.ClubCategory, t.EventID, t.UniquePerfID, t.Score
FROM [T05 All Throws Performances] AS t
WHERE (((t.Score) In (SELECT TOP 3 Score
FROM [T05 All Throws Performances]
WHERE AthleteID = t.AthleteID
ORDER BY Score DESC
)))
ORDER BY t.AthleteID, t.Score DESC;
[\code]
What this is doing is returning 700+700+650+650=2700, how do I drop the lowest duplicate score and only total 3?
