Update values in one table from another (1 Viewer)

[B Kava]

In MS Access 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.

I’ve attached the database (Access 2016), named Unrest.accdb, that has the two tables and query. It’s highly generalized from the real-world scenario. I greatly appreciate any help you can offer.

 

[theDBguy]

This might be possible without using VBA by using IIf() and perhaps some well constructed WHERE clause.

 

[Pat Hartman]

Allowing updates from two separate sources is the road to disaster. If your table is the "Master", why are you even taking updates from other sources? How can you tell which source has the correct value? If your table is not the master but you want to be able to override certain values, keep those columns in a separate table. That way, you can just import the replacement for your master and not try to update row by row.

 

[Gasman]

Crossposted https://www.utteraccess.com/topics/2063331/posts/2799525

 

[June7]

Also at https://stackoverflow.com/questions/72221705/updating-one-table-in-ms-access-with-data-from-another with suggestion:

SET Size = IIf(Master.Size Is Null, New_Data.Size, Master.Size)