Converting a query containing MAX into an UPDATE statement in Access

I've got a table with Years, Months, Scores and I created a query to isolate the MAX score for each month. Now I want to create an update query that will mark each item in a new column, but I'm not quite sure how given the MAX.

SELECT a.Year, Max(a.Score) AS MaxOfScore, a.Month FROM a GROUP BY a.Year, a.Month, a.Score HAVING (((a.Month) Is Not Null));

How do I write an update statement that adds "x" to a new column, a.High?

Thanks

Answers


I'm not sure that adding this as a static value to the table is what you really want to do but here's the code for that:

update a set a.high = b.MaxOfScore from 
a left join (
SELECT a.Year, Max(a.Score) AS MaxOfScore, a.Month
FROM a
GROUP BY a.Year, a.Month
HAVING (((a.Month) Is Not Null))
) b on a.year = b.year and a.month = b.month

What you'd probably want to do instead is just select the value with a query:

SELECT a.Year, a.Month, a.Score, b.MaxOfScore from
a left join (
SELECT a.Year, Max(a.Score) AS MaxOfScore, a.Month
FROM a
GROUP BY a.Year, a.Month
HAVING (((a.Month) Is Not Null))
) b on a.year = b.year and a.month = b.month

In MS Access SQL, update queries must be updateable and hence aggregate queries cannot be joined to a unit level table. However, you can use the Domain Aggregate functions. Also, if month and year are strings be sure to concatenate their values with enclosed single quotes:

UPDATE a
SET a.High = DMax("Score", "a", "Year=" & a.Year &  
                                " And Month=" & a.Month & 
                                " And Month Is Not Null");

Please note: Domain Functions are only available in SQL queries in the MS Access UI program and not in Jet/ACE SQL Engine called outside MSAccess.exe using ODBC/OLEDB.


Need Your Help

Click the poster image the HTML5 video plays?

jquery html5 video

I have an HTML5 video with a poster attribute. I would like to somehow set it up so that you can click on anywhere on the video element (the area of the poster image) and it will fire the play even...

T-SQL: Deciding between 2 WHERE clauses

sql sql-server tsql

I am creating a stored procedure in SQL Server 2008. The main query's WHERE clause could be either