Avatar of kay soo
kay soo asked on

UpdateQuery for Row Update To Another Table

Gd Day, I am trying to determine if there's a way for a Tbl1 query to run an individual row update to a linked Table2 based on matched row records from the table1 ?
Both Tables are structurally identical, fields data of both Tables "Client", "OrderDate" and "ProductNo" always identical when replicated.
Only Field1 to 3 are constant updating in Tbl1 daily, new client name always enter into Tbl2 only, therefore in this case, delete and append to make up for the update to Tbl 2 is out of the question.

Tbl1                                                                                            Tbl2
Client    OrderDate  ProductNo  Fld1   Fld2    Fld3             Client    OrderDate  ProductNo  Fld1   Fld2    Fld3
Client1  10-01-2018   10-1234A    A       100       X                Client1   10-01-2018   10-1234A                                              
Client2  10-02-2018   25-1857C    B       330       Y                Client2   10-02-2018   25-1857C                                                
Client3  10-03-2018   3320E-A1    C       550       Z                Client3    10-03-2018   3320E-A1                                                

Tbl2 Result after update query update from tbl1
Client           OrderDate      ProductNo      Fld1       Fld2          Fld3
Client1        1-Oct-2018      10-1234A          A           100             X
Client2        2-Oct-2018      25-1857C          B           330             Y
Client3        3-Oct-2018      3320E-A1          C           550             Z

Please note, the row update criteria for records are "Client", "OrderDate" and "ProductNo", Fields pending update in Tbl2 are actually way more then 3 as shown as example
The Update Query must be able to find the match of "Client", "OrderDate" and "ProductNo" in tbl2 to update Field 1 to 3+

Appreciate if anyone able to provide solutions to obtain the result.

Microsoft AccessSQL

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
kay soo

It works great, thx
Gustav Brock

You are welcome!

You could also have used a merge -
merge into tbl2 t2
using tbl1 t1
on (t1.client = t2.client and t1.orderdate = t2.orderdate and t1.productno = t2.productno)
when matched then
update set
t2.fld1 = t1.fld1,
t2.fld2 = t1.fld2,
t2.fld3 = t1.fld3;
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Gustav Brock

Not in Access SQL.