"Query must use updateable query" error message

[Marc De Schrijver]

I have a complex query that serves as a basis for joining to a table whose
fields need to be updated with values from fields in the complex query.
Trying to run the update query results in the "Query must use updateable
query" message.

The basic outline of my query is as follows:

UPDATE Table1
INNER JOIN ((SELECT DISTINCT OrderNr, Max(Price) AS MaxPrice FROM Table2
GROUP BY OrderNr) AS TempTable)
ON Table1.OrderNr = TempTable.OrderNr
SET Table1.CurrentPrice = TempTable.MaxPrice

In reality the subquery to which I am inner joining is far more complicated,
but this simplified version also results in a read-only subquery that serves
as a basis for joining to the table that is to be updated.

How can this be done? I assume it just needs to be written differently. Any
help would be greatly appreciated!!

-- Hans De Schryver

 

[Duane Hookom]

You won't be able to write this in Access SQL syntax. Your can try:

UPDATE table1
SET CurrentPrice = DMax("Price","Table2","OrderNr=" & OrderNr);

This will be slow but should work (assuming OrderNr is numeric).

 

[mac]

I just ran into this same issue yesterday. I found that the "source" query
(the one from which I want to draw the update values) must be an actual
table, not any type of query.

No matter how I messed around with the syntax, I got the same message you
are getting. Then, I instantiated the source query as a table. I was then
able to run the update statement.

Thanks,

Mac