Use max value from a table in a query from the same table.

[DBA-Meister]

I have the following simple query:

SELECT Last3MtchQry.[Thrower-Name], Int(Avg([Score-Avg])+0.5) AS Current3WkAvg, 672 - Int(Avg([Score-Avg])+0.5) AS CurrentHndyKap, Last3MtchQry.ID
FROM Last3MtchQry
GROUP BY Last3MtchQry.[Thrower-Name], Last3MtchQry.ID;



Every week I have to manually change the hard-coded 672 value. I want to change the hard-coded 672 to the max average value so that the person with that value ends up with a zero handicap. Anyone got any ideas how to do this?

[June7]

The average of what? Last week/month/season games, top 3? Is this average already calculated somewhere?

[DBA-Meister]

A separate query calculates the average score for each player for each week. The query used here queries that query to calculate the average of those three averages. For each player, this value is subtracted from the value for the player with the highest average. The difference is then used as the current week handicap.

[June7]

You want to know the highest 3-week average by a single player? Then each player's 3-week average is subtracted from that one player's average to provide each player with a handicap?

The ID of the high player is irrelevant, just need the highest average?

Build the 3-week average query without the handicap calc.

Build another query.

SELECT [Thrower-Name], DMax("Average", "3weekAvgQry") - [Average] AS CurrentHndyKap FROM 3weekAvgQry;

[Bob Fitz]

Not sure this is what you want, but you could try replacing the 672 with something like DMax("[Score-Avg]","[ Last3MtchQry]") which would give you :
SELECT Last3MtchQry.[Thrower-Name], Int(Avg([Score-Avg])+0.5) AS Current3WkAvg, DMax("[Score-Avg]","[ Last3MtchQry]")
- Int(Avg([Score-Avg])+0.5) AS CurrentHndyKap, Last3MtchQry.ID
FROM Last3MtchQry
GROUP BY Last3MtchQry.[Thrower-Name], Last3MtchQry.ID;

[DBA-Meister]

When I use your code, I get this: "The Microsoft Access database engine cannot find the input table or query 'Last3MtchQry'. Make sure it exists and that it's name is spelled correctly."

The query DOES exist and it is spelled correctly. Not sure what's going on here.

[June7]

Did you use the query builder Design View to construct?

[DBA-Meister]

I used Create/Query Design, then went to "SQL" view and pasted the sql you suggested.

[June7]

Reread my post 4. Did you build 2 queries as suggested?

[DBA-Meister]

I created new query MaxAvgScoreQry as follows:
SELECT max(t1.Current3WkAvg) AS MaxAvgScore
FROM Current3WkAvgQry AS t1;


Then I inserted "(Select MaxAvgScore from MaxAvgScoreQry)" in place of the hardcoded numerical value. Success. Thx for the help.