Updating one table in MS Access with data from another

Question

In MS Access 2016 I have a table named Master that periodically needs to gets updated with ‘updated’ data from a table named NewData. Each table has the same fields, except Master has one additional field named OTHER_SOURCES (explained further down). They each have an indexed unique id field named EVENT_ID. I’ve built an update query where the tables are joined one-to-one on the EVENT_ID field. In this query I have the fields in Master getting updated to the new values from the same fields in NewData if the TIMESTAMP field value is different. If the TIMESTAMP values haven’t changed, then those records do not get updated. This part is pretty straightforward and works fine.

However, I have end users that may make occasional changes to the values in the SIZE field of Master that need to be preserved and not overwritten with updated values from NEW_DATA. When a user makes a change in SIZE field, he documents the change with information obtained from other sources, which is stored in the extra field I mentioned earlier: OTHER_SOURCES. Here’s what I need to do, and I just can’t figure it out. Whenever a user has made a change to the SIZE field for a record, I need the update query to not override that value in the SIZE field, but still update the values in all the other fields (again, assuming the TIMESTAMP values are different between the two tables). It seems I need to use an IIF statement, but I’m thinking it needs to be done in VBA where I’m a bit of a hack. See screenshots. I greatly appreciate any help you can offer. enter image description here enter image description here

Answer 1

You can still proceed with the update, but update it to the same value as its previous value:

SET SIZE= IIF(nz(OTHER_SOURCES,'')<>'', MASTER.Size, NEW_DATA.Size)

This assumes that anything present in the MASTER.OTHER_SOURCES column indicates that the user has changed MASTER.Size. Note that MASTER.Size will never be updated from NEW_DATA.Size until someone (end user) removes MASTER.OTHER_SOURCES.