UpdateQuery for Row Update To Another Table

kay soo
kay soo used Ask the Experts™
on
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                                                
                                                                                                    Client4
                                                                                                    Client5

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
Client4
Client5


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.

Thx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can use this query:

UPDATE 
    Tbl1 
INNER JOIN 
    Tbl2 
    ON 
        (Tbl1.Client = Tbl2.Client) 
        AND 
        (Tbl1.OrderDate = Tbl2.OrderDate) 
        AND 
        (Tbl1.ProductNo = Tbl2.Product) 
SET 
    Tbl2.Fld1 = Tbl1!Fld1, 
    Tbl2.Fld2 = Tbl1!Fld2, 
    Tbl2.Fld3 = Tbl1!Fld3;

Open in new window

This will require a unique index on the fields Client-OrderDate-ProductNo

Author

Commented:
It works great, thx
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!
awking00Information Technology Specialist

Commented:
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;
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Not in Access SQL.

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