Update target table column value when source table column value is different

skull52 used Ask the Experts™
I have an access table tbl_Items_UPC (Target Table) with a QTYONHND column that I want to only update from dbo_SSG_UPC_SCAN (Source Table) when the source value changes. I work mostly with SQL and it has a MERGE function that does that very well. I suspect that I would have to use an update query in access to accomplish this, I have tried a couple of queries but it didn't seem to work. I am using Access 2016
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Application Developer
Sounds like you need a select query that joins tbl_Items_UPC to dbo_SSG_UPC_SCAN on the key field(s) and has a criteria in the dbo_SSG_UPC_SCAN.QTYONHND column  <> tbl_Items_UPC.QTYONHND .  That should return the rows in dbo_SSG_UPC_SCAN that have QTYONHND  values different from those in tbl_Items_UPC.  Then make it an update query and set the tbl_Items_UPC.QTYONHND update cell to dbo_SSG_UPC_SCAN.QTYONHND.
Hamed NasrRetired IT Professional

Agree with Paul's comment.
Here is an application to table a(aid, f) as source, and table b(aid, f1) as target.
Need to update f1 in target when f1 is different.
UPDATE b AS target INNER JOIN a AS source ON target.aid = source.aid SET target.f1 = source.f1
WHERE (((target.f1)<>[source].[f1]));

Open in new window

skull52IT director


Thanks Paul, I should have figured that out, that is how I would have done in in SQL if I didn't use  SQL's MERGE Function

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial