Prev: update two fields at once
Next: Crosstab query...
From: demcknight60 on 23 Jan 2010 14:58
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;
From: bhicks11 via AccessMonster.com on 23 Jan 2010 17:28
You only need to query the data sorted descending on Year for Top 1.

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

demcknight60 wrote:
>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;

--
Message posted via http://www.accessmonster.com

 | 
Pages: 1
Prev: update two fields at once
Next: Crosstab query...