A97 Nested Query??

[Jim Sloan]

I have a table with fields, PlayerID, PlayerScore, GameDate. I want to
retrieve and eventually average the most recent 4 scores for each player.

Could sure use a little help getting started.

--
Jim

Today is the first day of the rest of your life!
But, so was yesterday and look how that turned out!

[John Spencer (MVP)]

I think that the following UNTESTED SQL may work.

SELECT PlayerID, PlayerScore, GameDate
FROM TABLEname as A
WHERE GameDate IN
(SELECT TOP 4 B.GameDate FROM
TableName as B
WHERE A.Player = B.Player
ORDER BY B.GameDate DESC)

[Jim Sloan]

Thank you very much. This is what I got to work.

SELECT A.PlayerID, A.AdjScore, A.GameDate
FROM Scores AS A
WHERE (((A.GameDate) In (SELECT DISTINCT TOP 4 A.GameDate
FROM Scores as B
ORDER BY A.GameDate DESC)))
ORDER BY A.PlayerID, A.GameDate DESC;

The Report even looks good!!

Thanks again

Jim

"John Spencer (MVP)" <spence...@SPAMNOT.umbc.edu> wrote in message
news:3E19FC5A...@SPAMNOT.umbc.edu...