MS Access: Update a table field from a query field

Question

I have a field in a table called "CaseDelay" and a field in a query called "Delay." Both are numbers.

Don't know the best procedure to get the query to run and the field value to update the table field.

Additionally, the field in the query is a calculated field, and I read on Microsoft's website that syntax such as UPDATE won't work with calculated fields.

The expressions that are in the query are difficult nested IIF statements that I couldn't figure out how to write in VBA, but could do it in a query expression.

Answer 1

You'll first need to specify how the records in the query relate to those in the table, such that an update query can unambiguously identify & update the appropriate record in your table for each record in your query.

Typically this would be achieved using unique keys present in both the table & query which may be used to pair up the records in the two datasets.

Once this is ascertained, assuming that the query does not use aggregation, you can use a straightforward update query with a join between your existing query & table, e.g.:

update YourTable inner join YourQuery on YourTable.ID = YourQuery.ID
set YourTable.CaseDelay = YourQuery.Delay