average of best 10

[George]

Hi,
I have a data base of golf scores for a relative small
group, keeping track of the scores and dates of the
scores.
How can I extract with a query the average of the 10 best
scores from their last 20 games.

Thanks
George

 

[Jeff Boyce]

George

Take a look at the "Top" property of the query to select the "best 10".

 

[George]

My difficulty is not so much extracting the top 10 but
extracting the last 20 by dates and the dates may vary
for each entry.
Can anyone help?

George

 

[John Spencer (MVP)]

Stacked queries, may be one way to do this.


Get the last 20 scores for each golfer

SELECT GolferID, Score, PlayDate
FROM Table
WHERE PlayDate IN
(SELECT TOP 20 T.PlayDate
FROM Table as T
WHERE T.GolferID = Table.GolferID
ORDER BY T.PlayDate DESC)

Get the top 10 Scores of that saved query, Saved as QueryOne

SELECT GolferID, Avg(Score) as AvgScore
FROM QueryOne
WHERE PlayDate IN
(SELECT TOP 10 PlayDate
FROM QueryOne as Q
WHERE Q.GolferID = QueryOne.GolferID
ORDER BY Q.Score, Q.PlayDate Desc)
GROUP BY GolferID

This assumes that they only play ONE game per date. If more than one game can
be played per day, then you will probably need some way to order uniquely.


SELECT