Re: Formula or code required

[Arvin Meyer [MVP]]

Have a look at this MS KB article:

http://support.microsoft.com/kb/153747/en-us
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"rwrees" <rwr...@discussions.microsoft.com> wrote in message
news:E0A91A87-972C-4433...@microsoft.com...
>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.

[Ken Sheridan]

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

[Jerry Whittle]

SELECT S1.GolferName, S1.Score
FROM SCORES AS S1
WHERE S1.Score In
(SELECT TOP 5 S5.Score
FROM Scores AS S5
WHERE S5.Score = S1.Score
ORDER BY S5.Score DESC)
ORDER BY S1.GolferName, S1.Score DESC;

Something like the above with the proper table and field names might work.
One problem is that the Top function shows ties. For example if the golfer
has 2 scores of 83 and they would be their fifth lowest score, both would
show up.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

[Jerry Whittle]

Now why didn't I think of that! Thanks Ken.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

[Graham Mandeno]

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" <JerryW...@discussions.microsoft.com> wrote in message
news:2327F6CB-44A5-4A9B...@microsoft.com...

[Graham Mandeno]

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" <Graham....@nomail.please> wrote in message
news:%23SQoDXG...@TK2MSFTNGP02.phx.gbl...

[James A. Fortune]

rwrees wrote:
> 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.

Here's how I would go about it. There may be simpler ways.

Scores:
ID Score GolferName
1 79 A
2 83 C
3 73 B
4 86 C
5 75 B
6 72 A
7 75 B
8 80 C
9 81 C
10 75 B
11 80 A
12 73 B
13 77 B
14 72 A
15 80 C
16 72 B
17 79 C
18 80 B
19 75 B
20 79 A
21 81 A

qryRankByPlayerScore:
SELECT Scores.Score, (SELECT Count(A.ID) FROM Scores AS A WHERE A.Score
< Scores.Score AND A.GolferName = Scores.GolferName)+(SELECT Count(A.ID)
FROM Scores AS A WHERE A.Score = Scores.Score AND A.ID < Scores.ID AND
A.GolferName = Scores.GolferName)+1 AS RankByPlayerScore, GolferName
FROM Scores;

!qryRankByPlayerScore:
Score RankByPlayerScore GolferName
79 3 A
83 5 C
73 2 B
86 6 C
75 4 B
72 1 A
75 5 B
80 2 C
81 4 C
75 6 B
80 5 A
73 3 B
77 8 B
72 2 A
80 3 C
72 1 B
79 1 C
80 9 B
75 7 B
79 4 A
81 6 A

qryAverageScoreBest5ByPlayer:
SELECT GolferName, Avg(Score) As AverageScore FROM qryRankByPlayerScore
WHERE RankByPlayerScore<=5 GROUP BY GolferName;

!qryAverageScoreBest5ByPlayer:
GolferName AverageScore
A 76.4
B 73.6
C 80.6

Maybe a separate query can be used to weed out the most recent eight
fixtures for each player first. I don't think the ID values have to be
contiguous for this method to work.

James A. Fortune
MPAP...@FortuneJames.com

My alma mater, Oakland University, has two regulation length 18 hole
golf courses on campus.

[Ken Sheridan]

Graham:

Doh! Obvious when you think about it. We can do it with two queries,
though. First one, qryScoresNumbered say, which numbers the rows in score
order, making use of a primary key ScoreID column (an autonumber is fine for
this) to distinguish between identical scores:

SELECT GolferName, Fixture, Score,
(SELECT COUNT(*)
FROM Scores AS S2
WHERE S2.Score <= S1.Score
AND ( S2.ScoreID <= S1.ScoreID
OR S2.Score <> S1.Score)) AS RowNumber
FROM Scores AS S1
ORDER BY Score, ScoreID;

You can include any columns from the table in this, e.g. Fixture as above,
as the ScoreID identifies them all as distinct so the DISTINCT option isn't
needed. Then create another query based on the above:

SELECT GolferName, Fixture, Score
FROM qryScoresNumbered As SN1
WHERE RowNumber IN
(SELECT TOP 5 RowNumber
FROM qryScoresNumbered As SN2
WHERE SN2.GolferName = SN1.GolferName)
ORDER BY GolferName,Score;

Where there are matching 5th lowest scores some fixtures are discarded
arbitrarily; it’s the ones with the higher ScoreID values in fact.

Ken Sheridan
Stafford, England

[Ken Sheridan]

Jerry:

Because, unlike me, you were thinking straight! Hopefully my latest effort
has cracked it.

Ken Sheridan
Stafford, England

[Granny Spitz via AccessMonster.com]

Ken Sheridan wrote:
> We can do it with two queries,

(snip)

> Where there are matching 5th lowest scores some fixtures are discarded
> arbitrarily; it’s the ones with the higher ScoreID values in fact.

Splendid job on this solution, Ken! I didn't think it could be done with
plain SQL either, but you figured it out!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200611/1