Top Scores 1

[TrekBiker]

A scores table looks like this, recording each player's score on a number of competition days.

CompDate, Player, Score

I'd like a query that picks just the winners on each date. So shows Date, winner (or ties) and winning score.

 

[dhookom]

The simplest method is to first create a query of top scores by date:

qgrpTopScores

SELECT CompDate, Max(Score) as MaxScore
FROM tblScores
GROUP BY CompDate;

Then create another query using the table and new query:

SELECT S.*
FROM tblScores S INNER JOIN qgrpTopScores Q ON S.CompDate = Q.CompDate and S.Score = Q.MaxScore

This can also be done in a single query.

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

Thanks Duane, as ever.

I substituted S and Q for my table and field names but then it did the job.