Update with aggregate from same table

[AlbertMC2]

Hi

I have Access 2007 with a table (tblCust) containing a few fields. The 2 most important are the "ID" which is unique (PK) and a "fldValue"
"fldValue" has numerical values in some of the records while others are blank.
I would like to insert the maximum value of "fldValue" into the records that have no value.



So far I have this which does not work:

UPDATE tblCust 
SET tblCust.fldValue = (SELECT max(tblTemp.fldValue)
                                from tblCust tblTemp)
WHERE tblCust.fldValue is null;

This gives me an "Operation must use an updateable query"

Any ideas would be appreciated.

[JoeM]

In an update query, all objects involved must be updateable in order for it to work. Aggregate queries are not updateable. Even though you are not trying to update the Aggregate Query, since it is used by the query, it makes it unupdateable (I find this to be a nuisance myself!).

You can write the results of your Aggregate Query to a temp table, and then use that temp table in your Update Query instead, and then it should work.

Note, most of the time, if you can calculate the value you need in a query, there is no need to write the value back to the table. Doing so may actually undermine the dynamic nature of your database and could hurt database integrity (de-normalizes your database).

[June7]

I agree, storing aggregate data is usually a bad idea. A basic principle of relational database is save raw data, calculate on reports.

However, might be able to do what you want without temp table by using DMax domain aggregate function. http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

[AlbertMC2]

Hi

Thanks for the replies. This update will, hopefully, only occur once so I am not really worried about data integrity.
Having said that, I assume updating the table this way will be quicker. Surely it is quicker to access the fields of one record than to access the field of all the records and find the max value?

Can I create a temp table in the same query using a SELECT INTO or am I going to have to create 2 queries? Or is it going to be faster to just do this with VBA using the DMax or temp table?

[June7]

How else will the program know which is max if it doesn't compare all values in the field?

One source for sql tutorial http://www.w3schools.com/sql/default.asp