update query with subqueries, not updateable (1 Viewer)

[Domski74]

Hi All

I'm trying to set a day count based on the number of days in the year and take account of possible leap years. The table only ever contains a years cumulative data and the day count needs to run from the first of April and be flexiable enough not to need maintenance to account for a new year. I've witten the below which works fine in a select query butI get a not updateable error in the update?

UPDATE Pharmacy SET Pharmacy.MRN = "UK" & (DateSerial((select year(min([RIODate])) from Pharmacy)+1,3,31)-DateSerial((select year(min([RIODate])) from Pharmacy),4,1))-(DateSerial((select year(min([RIODate])) from Pharmacy)+1,3,31)-[RIODate])+1;

Please can anyone advise how to correct this so it will work?

Many thanks

Dominic

 

[khawar]

Make a temporary table from selected data and then main table with data from that temporary table

 

[Pat Hartman]

Queries that aggregate data are not updateable. Period.

I think your query should be updateable since the column being updated is explicitly identifiable. It is only the value that is "aggregated". But the Jet/ACE developers don't see it my way.