select team based on max record .. query

[mabughazza]

hi ..

I built this query :

SELECT X.team AS Winner_team_round, Max(X.SumOfPOINTS) AS MaxOfSumOfPOINTS
FROM (SELECT Sum(Rounds_points.POINTS) AS SumOfPOINTS, Sum(Rounds_points.VALUES) AS SumOfVALUES, Sum(Rounds_points.TOTAL) AS SumOfTOTAL, Rounds_points.R_ID, Instructors.team, Sum(Rounds_points.SCIENCE) AS SumOfSCIENCE, Sum(Rounds_points.GOLD) AS SumOfGOLD, Sum(Rounds_points.CHALLENGE) AS SumOfCHALLENGE FROM Instructors INNER JOIN Rounds_points ON Instructors.[I_ID] = Rounds_points.[I_ID] GROUP BY Rounds_points.R_ID, Instructors.team HAVING ((((Instructors.team) Like "blue" OR (Instructors.team) Like "red" OR (Instructors.team) Like "yellow" OR (Instructors.team) Like "green")) AND (Rounds_points.R_ID) Like "1" ) ORDER BY Rounds_points.R_ID)  AS X
GROUP BY X.team;

and the result is :


I want to return only RED (WHICH IS THE MAX) ..

I already succeed to return only 240 by using this :

SELECT Max(X.SumOfPOINTS) AS MaxOfSumOfPOINTSFROM (SELECT Sum(Rounds_points.POINTS) AS SumOfPOINTS, Sum(Rounds_points.VALUES) AS SumOfVALUES, Sum(Rounds_points.TOTAL) AS SumOfTOTAL, Rounds_points.R_ID, Instructors.team, Sum(Rounds_points.SCIENCE) AS SumOfSCIENCE, Sum(Rounds_points.GOLD) AS SumOfGOLD, Sum(Rounds_points.CHALLENGE) AS SumOfCHALLENGE FROM Instructors INNER JOIN Rounds_points ON Instructors.[I_ID] = Rounds_points.[I_ID] GROUP BY Rounds_points.R_ID, Instructors.team HAVING ((((Instructors.team) Like "blue" OR (Instructors.team) Like "red" OR (Instructors.team) Like "yellow" OR (Instructors.team) Like "green")) AND (Rounds_points.R_ID) Like "1" ) ORDER BY Rounds_points.R_ID)  AS X;

but I want return RED not the 240 ..

any one can help ???

another thing I want to save the result in public string too using VBA .. SO I can do some more coding on it ..

thanks in advance ...

[JamesDeckert]

sort by maxsumofpoints in descending order, then choose top 1 record.
You can turn off 'show' if you don't want to see 240.
To get the value in VBA, open the query in VBA as a recordset and you first (and only) record will be the one you want.