find the avg for the last 3 values for each record using a query? 1

[sna1maa14]

Here is my situation. I have to assign an avg to each player based of their last three games played. I have a table with scores for each game by person and the month played. I need to take their last 3 scores and avg them for each player. So my question is how do I get player 3 to only avg his last there scores using a query? Here is my table and sample data.

PlayerID, Score, Date
1 20 1/1/07
1 25 3/1/07
1 30 6/1/07
2 25 1/1/06
2 15 1/1/07
3 10 1/1/07
3 15 2/1/07
3 20 3/1/07
3 30 4/1/07
3 25 7/1/07

Thanks
sna1maa

 

[dhookom]

Next time, please provide your actual table and field names so answers can be more specific. Try

SELECT tblPlayerScores.*
FROM tblPlayerScores
WHERE (((tblPlayerScores.Date) In (SELECT TOP 3 Date FROM tblPlayerScores s WHERE s.PlayerID = tblPlayerScores.PlayerID ORDER BY Date DESC)));

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Typed, untested:

SELECT A.PlayerID, Avg(A.Score) AS Average, Min(A.Date) & '-' & Max(A.Date) AS Period
FROM yourTable AS A INNER JOIN yourTable AS B ON A.PlayerID=B.PlayerID AND A.Date<=B.Date
GROUP BY A.PlayerID
HAVING Count(*)=3

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sna1maa14]

Sorry I have not been able to log in because of the San Diego Wildfires.

dhookom, your code was exaclty what I need. Thanks!

SELECT tblScores.*
FROM tblScores
WHERE (((tblScores.Date) In (SELECT TOP 3 Date FROM tblScores s WHERE s.PlayerID = tblScores.PlayerID ORDER BY Date DESC)));

 

[dhookom]

sna1maa14,
I hope you and your family/friends have been safe.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]