Hi Ken
I just thought about it some more, and DISTINCT isn't always going to
work...
Let's say the player's scores are: 73, 73, 78, 80, 80, 80, 92, 96
TOP 5 will return six values: 73, 73, 78, 80, 80, 80
DISTINCT will reduce this to three: 73, 78, 80.
I think the only way around this is to write a function to open a recordset
filtered by PlayerID and ordered by Score and read only the first five
records.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand
Graham Mandeno said:
Yes - nice use of DISTINCT
Just a small point that might confuse the OP... in golf, the best score is
the *lowest*, so the ORDER BY clause should omit the "DESC" predicate.
--
Cheers!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Jerry Whittle said:
Now why didn't I think of that! Thanks Ken.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
You can get round the problem which Jerry mentioned of someone having
equal
5th lowest scores by using the DISTINCT option, provided hat you don't
want
to return any other columns (such as the fixture in which they achieved
the
score) which would make the rows non-distinct:
SELECT DISTINCT GolferName, Score
FROM Scores AS S1
WHERE Score IN
(SELECT TOP 5 Score
FROM Scores As S2
WHERE S2.GolferName = S1.GolferName
ORDER BY Score DESC);
Ken Sheridan
Stafford, England
:
I have been made handicap secretary for my golf society and used
Access to
record individual scores and produce required reports.
Our winter competition is based on an individuals best 5 scores out of
a max
of 8 fixtures. therefore some individuals might only play 6 or 7
fixtures.
How can i get Access identify and select the best 5 scores for an
individual.
many thanks.