Listing Max values containing the week the value occured.

[Meinthecorner]

Right I'm stumped.

I run a small football based competition at work. The players predict the outcome of random matches throughout the leagues.

What I want to be able to do is run a query to simply show each PlayerID against that the highest score they have got and the week that occoured.

Maybe my table structure is wrong but what I have currently are two tables.

The first contains the PlayerID and Team Name

The second contains EntryID, PlayerID, WeekNumber and Points scored.

I just can't work out how to show the Max score and the week. I keep ending up with a query that shows 80 Results (There are 40 Players) with the Top Score for Week 1 and Week 2. At the moment this will just grow by 40 every week.

So in summary I want it to return 40 Results, with the Team name, week and max points scored.

I thought it would be pretty simple, but its driving me nuts!

 

[Scooterbug]

Played around with the idea..came up with creating a function that you can call from the query:

Public Function GetScoreAndWeek(lPlayerID As Long) As String
Dim iScore As Integer
Dim iWeek As Integer
iScore = DMax("[Points]", "tblResults", "[PlayerID]=" & lPlayerID)
iWeek = DLookup("[WeekNumber]", "tblResults", "[Points]=" & iScore & " and [PlayerID]=" & lPlayerID)
GetScoreAndWeek = "Score: " & iScore & " In Week: " & iWeek
 
End Function

Of course you will have to put in the correct field names.

Make a query based off of the Player table and put a field like so:

BestScore: GetScoreAndWeek([PlayerID])

Edit: Fixed typo in
GetScoreAndWeek = "Score: " & iScore & " In Week: " & iWeek