Update access table with values from another table using VBA

Question

I have two tables, table 1 and table 2, in a database. I am trying to update table 1 using VBA code based on data in table 2.

Example:

Table 1

PartNo  Price  Description
--------------------------
A      100    
B      200      Bad
C      300

Table 2

PartNo  Price  Description
--------------------------
A        700
B        200      Good
D        900      Used

After the update, table1 should be updated with those prices or descriptions that have changed where table1 PartNo = table 2 PartNo , and add any new items that were present in table 2.

Table 1

PartNo  Price  Description
--------------------------
A      700
B      200      Good
C      300
D      900      Used

I can't seem to get it quite right, appreciate the help.

Answer 1

You can do it with two statements, an update and an insert like this:

Update:

UPDATE Table1 
INNER JOIN table2 
 ON(table1.partNo = table2.PartNo)
SET table1.price = table2.price,
    table1.description = table2.description

And then an insert:

INSERT INTO table1 (PartNo,Price,Description)
SELECT PartNo,Price,Description FROM table2 t
WHERE NOT EXISTS(SELECT 1 FROM table1 s
                 WHERE t.PartNo = s.PartNo)