D
demcknight60
In the below query I want the max year field to pich the associated school
that the coach was affiliated with that max year. Currently the query gives
the last year that the coach coached and the highest alpha Name of schools -
which not what I want.
Example:
Coach Year School
Urban Meyer 2009 Florida
Urban Meyer 2002 Utah
Urban Meyer 2000 Bowling Green
Query now results in
Coach Maxyear School
Urban Meyer 2009 Utah
should be
Coach Maxyear School
Urban Meyer 2009 Florida
SELECT DISTINCTROW [Coaching Performance Step 2].Coach,
Max(Coaches_Teams_Seasons.[Year_ End]) AS [MaxOfYear_ End],
Last(Coaches_Teams_Seasons.School) AS LastOfSchool
FROM Coaches_Teams_Seasons INNER JOIN [Coaching Performance Step 2] ON
(Coaches_Teams_Seasons.School = [Coaching Performance Step 2].School) AND
(Coaches_Teams_Seasons.Coach = [Coaching Performance Step 2].Coach)
GROUP BY [Coaching Performance Step 2].Coach;
that the coach was affiliated with that max year. Currently the query gives
the last year that the coach coached and the highest alpha Name of schools -
which not what I want.
Example:
Coach Year School
Urban Meyer 2009 Florida
Urban Meyer 2002 Utah
Urban Meyer 2000 Bowling Green
Query now results in
Coach Maxyear School
Urban Meyer 2009 Utah
should be
Coach Maxyear School
Urban Meyer 2009 Florida
SELECT DISTINCTROW [Coaching Performance Step 2].Coach,
Max(Coaches_Teams_Seasons.[Year_ End]) AS [MaxOfYear_ End],
Last(Coaches_Teams_Seasons.School) AS LastOfSchool
FROM Coaches_Teams_Seasons INNER JOIN [Coaching Performance Step 2] ON
(Coaches_Teams_Seasons.School = [Coaching Performance Step 2].School) AND
(Coaches_Teams_Seasons.Coach = [Coaching Performance Step 2].Coach)
GROUP BY [Coaching Performance Step 2].Coach;