Access SQL Update with Max not working

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

In Access I am getting the error "Your query does not include the specified expression ‘LastRenewalDate’ as part of an aggregate function" for the following

UPDATE t_Licence INNER JOIN t_Renewals ON t_Licence.LicenceNumberPK = t_Renewals.LicenceNumberFK SET t_Licence.LastRenewalDate = Max(t_Renewals.RenewalDate)
WHERE (((t_Renewals.LicenceNumberFK)=[t_Licence].[LicenceNumberPK]));

I previously ran the query without the Max in it and it worked fine

UPDATE t_Licence INNER JOIN t_Renewals ON t_Licence.LicenceNumberPK = t_Renewals.LicenceNumberFK SET t_Licence.LastRenewalDate = t_Renewals.RenewalDate
WHERE (((t_Renewals.LicenceNumberFK)=[t_Licence].[LicenceNumberPK]));
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Try moving the MAX into an inner query.

UPDATE t_Licence INNER JOIN
(
select LicenceNumberFK, max(RenewalDate) maxRenewalDate
from t_Renewals group by LicenceNumberFK
) outer_t_Renewals
ON t_Licence.LicenceNumberPK = outer_t_Renewals.LicenceNumberFK SET t_Licence.LastRenewalDate = outer_t_Renewals.maxRenewalDate
WHERE outer_t_Renewals.LicenceNumberFK)=[t_Licence].[LicenceNumberPK];

Open in new window

Distinguished Expert 2017
Commented:
No query that includes aggregation is updateable.  Therefore, you CANNOT use Max() or any other aggregate function in an update query.

Storing aggregated data violates normal forms so SQL doesn't go out of its way to attempt to implement things that violate normal forms.

If you insist on storing this data (not recommended at all), Run a make table query to find the Max() values.  Then use a query that joins the table to be updated with the temp table containing the max() values to do the update.

Keep in mind that the reason for not storing this data is not arbitrary.  Storing this data means that if someone updates the source table with a higher value than you have stored, your stored Max() value will be inaccurate.  Better practice is to get the max() value at the time you need it so it is always as fresh as possible.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial