Query input value to determine another field results

[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;

 

[bhicks11 via AccessMonster.com]

You only need to query the data sorted descending on Year for Top 1.

Bonnie
http://www.dataplus-svc.com

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;