data append & overwrite by 1 step

[aw]

Could anyone can help?!


I have 2 tables :

Table 1 : tbl_customer master

Cust_code Customer Address
AA001 ABC Ltd 1000, BB Road..


Table 2 : excel_customer update

Cust_code Customer Address
AA001 ABC JV Ltd 1003, AA Road..


Questions :
How I can update table 1’s records base on table 2? i.e. overwrite (if
already exist)& append (if record not present previously)

Thx a lot in advance.

 

[John W. Vinson]

Could anyone can help?!


I have 2 tables :

Table 1 : tbl_customer master

Cust_code Customer Address
AA001 ABC Ltd 1000, BB Road..


Table 2 : excel_customer update

Cust_code Customer Address
AA001 ABC JV Ltd 1003, AA Road..


Questions :
How I can update table 1’s records base on table 2? i.e. overwrite (if
already exist)& append (if record not present previously)

Thx a lot in advance.

Left Join Table2 to Table1: choose the option "show all records in Table2 and
matching records in Table1". Change the query to an Update query and update
each field in Table1 to the value in Table2:

UPDATE Table2 LEFT JOIN Table1
ON Table1.Cust_Code = Table2.Cust_Code
SET Table1.Cust_Code = Table2.Cust_Code,
Table1.Customer = Table2.Customer,
Table1.Address = Table2.Addresss,
<etc>

Back up your database first of course!!!

 

[aw]

Thanks!

Besides how can I append ONLY the data that are new for table 1 (i.e. will
NOT amend any data that present for both tables)

 

[John W. Vinson]

Thanks!

Besides how can I append ONLY the data that are new for table 1 (i.e. will
NOT amend any data that present for both tables)

That would be a simple append query, with no join. Perhaps you could post your
current SQL and indicate what results you want, and what (if anything) is the
Primary Key or each table.

 

[PieterLinden via AccessMonster.com]

That would be a simple append query, with no join. Perhaps you could post your
current SQL and indicate what results you want, and what (if anything) is the
Primary Key or each table.

Umm... no it wouldn't. It would be a Find Unmatched query turned into an
append query, right, since the OP said he didn't want to overwrite his
original data?

 

[John Spencer]

TRY the following.

UPDATE Table2 LEFT JOIN Table1
ON Table1.Cust_Code = Table2.Cust_Code
SET Table1.Cust_Code = Nz(Table1.CustCode,Table2.Cust_Code)
, Table1.Customer = NZ(Table1.Customer,Table2.Customer)
, Table1.Address = Nz(Table1.Address,Table2.Address)
....

BACKUP your database first. If your text fields allow zero-length strings
this may not work for you and you will have to change the text fields to read
more like
Table1.Customer = IIF(Table1.Customer & "" = "",Table2.Customer,Table1.Customer)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County