Average of last 5 results

[Grant]

Hi, I have a BD with the results for and scores of a game
that I play. I curently have 125 players. Each player
has an average score for the games that they have played.
Some have players have had over 50 games over the past
few years. I want to be able to be able to get average
score of their last 5 results only. In addition i alos
want to give in the same collumn the average of the
players who have played less than 5 games. Each game has
an individual number which is assending based on the date
of the game.

Can someone help??

 

[Michel Walsh]

Hi,




SELECT a.PlayerID, a.Result
FROM myTable As a INNER JOIN myTable As b
ON a.PlayerID=b.PlayerID and a.DateTime <= b.DateTime
GROUP BY a.PlayerID, a.Result
HAVING COUNT(*)<= 5



is returning the 5 (at most 5) latest results, per player.



SELECT PlayerID, AVG(result)
FROM (
SELECT a.PlayerID, a.Result
FROM myTable As a INNER JOIN myTable As b
ON a.PlayerID=b.PlayerID and a.DateTime <= b.DateTime
GROUP BY a.PlayerID, a.Result
HAVING COUNT(*)<= 5 ) As z
GROUP BY PlayerID



makes the final aggregate (AVG). You can use a saved query rather than a sub
query in the FROM clause, as done here.



Hoping it may help,
Vanderghast, Access MVP