Update with Values from Fields in Another Table in Ms Access

Ms Access uses Jet engine, which is different in somewhere from T-SQL. Here is the way to update a field in Table_1 with the values from field in Tab_B:

UPDATE Table_1
INNER JOIN Tab_B ON Tab_B.Key_Field_In_B = Table_1.Key_Field_In_1
SET Table_1.Target_Field = Tab_B.From_Field
WHERE Tab_B.Condition_Field = 'Something'

Sometimes, you may still get error message: Operation must use an updateable query. Most likely, the join key of either table are not unique key, as a result, the join action does generate more rows than that in Table_1. Since this is an update action, this does caused the error.


http://www.fmsinc.com/microsoftaccess/query/snytax/update-query.html