Access Query finding latest entry based on numbers

[FameAsser]

So I have searched everywhere and can find half of the answer but not the other half. :banghead:

I have a table of predictions. For MatchID #1, PlayerID 56 predicted twice, so I need to take the latest prediction from them, but I also need everyone else's predictions.

I have attached how it looks

How can I, in a query, show Prediction #2 and #4 only...discarding #1 because on that PlayerID, they have superceded their prediction with #2...this will obviously need to work on much more data than this test.

I know I can use Top1 at some point, but that would mean I would need to strip out each player individually

:banghead:

 

[CJ_London]

try something like

SELECT p.* 
FROM tblPredictions P INNER JOIN (SELECT max(Predict) as Latest FROM tblPredictions GROUP BY MatchID, PlayerID) M on P.Predict=M.Latest

 

[FameAsser]

CJ - You are an absolute Superstar!

I knew I needed to Subquery somewhere along the line but couldn't get my head around it.

Thank you again