hi ..
I built this query :
Code:
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 :
Code:
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 ...