We help IT Professionals succeed at work.

Access Updated Query Using MAX

shieldsco
shieldsco asked
on
I'm using the following code and receive:

Image
UPDATE tblExcelProductivity INNER JOIN tblAdjDupGoals ON (tblExcelProductivity.FieldOffice = tblAdjDupGoals.FieldOffice) AND (tblExcelProductivity.FullName = tblAdjDupGoals.FullName) AND (tblExcelProductivity.Mnth = tblAdjDupGoals.Mnth) SET tblExcelProductivity.Goal = 0
WHERE (((tblExcelProductivity.Goal)=Max([tblExcelProductivity]![Goal])));

Open in new window

Comment
Watch Question

View Solution Only

EE Topic Advisor
SILVER EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
The error message is accurate,  it is NOT possible to include aggregate functions within a WHERE clause

Think of the sequence of a typical, select query with no aggregations ...

SELECT
FROM
WHERE

To perform aggregation you ADD a GROUP BY clause

SELECT
FROM
WHERE
GROUP BY  -- aggregations here

and you ALSO need to add another clause to filter by the aggregations

SELECT
FROM
WHERE
GROUP BY  -- aggregations here
HAVING -- filter by aggregate values here

I.E. you cannot filter by MAX(...)  which is an aggregate function in a WHERE, you can do this in a HAVING clause

But I doubt you need either a GROUP BY or HAVING clause in our update query. While the following is a pure guess, perhaps something like the following will help: 
UPDATE tblExcelProductivity
INNER JOIN tblAdjDupGoals ON tblExcelProductivity.FieldOffice = tblAdjDupGoals.FieldOffice
 AND tblExcelProductivity.FullName = tblAdjDupGoals.FullName
 AND tblExcelProductivity.Mnth = tblAdjDupGoals.Mnth

SET tblExcelProductivity.Goal = 0
WHERE tblExcelProductivity.Goal = (select Max([Goal]) from tblExcelProductivity)

Open in new window

nb: BEFORE you attempt an update, make sure it selects the wanted rows, try a select like the following first
SELECT tblExcelProductivity.Goal 
WHERE tblExcelProductivity.Goal = (select Max([Goal]) from tblExcelProductivity)

Open in new window