Update a table using a calculated field in a query

[sh5mg]

I have 2 tables:




T1 - overall info regarding some transaction


T2 - breakdown of the transaction by item and the status of where it is (currently "open" vs "closed")


I have run a query that determines based on the status in T2, what the overall status of the transaction is in T1 (ie if there are 2 line items, and 1 is "open" and the other is "closed" the over all status is "open") -(i do this via iif- open-1 closed-0, sum them by ID, and then if >0, "open", otherwise "closed")


I want to run an update query that updates the status column (linked by the ID),


so far I have tried:


UPDATE [T1] INNER JOIN [Query1] ON [Query1].[ID] = [T1].[ID] SET [Query1].[Overall Status] = [T1].[Overall Status];


I always get the error "Operation must use an updateable query"


Unfortunately, this is being done in ms Access, but any suggestions would be great!

[orange]

See if this Allen Browne article helps narrow the issue.
Good luck.

[June7]

Saving calculated data, especially aggregate data, is usually unnecessary and bad design. If you can calculate it for UPDATE effort then can calculate when needed.

Might be able to get the calculated result with DLookup() or DCount() expression in UPDATE query.

Otherwise, will likely involve VBA code.

[Gicu]

If you must update the field in the table you can do it using a temporary local table. Convert the Update query into a Make Table containing the ID and [Overall Status] or even better create the temp local table and use a Delete/ Append (with the Append having the calculation from the current Update one) sequence as Make Tables will bloat your db. Now create a new Update query using the temporary table and T1 that should be updateable. So you'll end up with a sequence of three queries (delete/append/update) instead of one update.

Cheers,