SQL Append Query

[Guest]

One of you MVP guys helped me out with the SQL code to update one table using
another table (thx for that). Now, I want to add code to append any records
found in the update table that do not exist in the main table. Hopefully it's
possible to combine them and only have to run one query to update and append
the new customer table.

I'm using Access 2003

I have a table called Customers and a table called lnk_Customers (Linked
Table from Excel). This is the code I have to update the main table and it
works perfectly...

UPDATE Customers INNER JOIN lnk_LinkedCustomers ON
Customers.AcctNum=lnk_LinkedCustomers.AcctNum SET Customers.AlphaName =
lnk_LinkedCustomers.AlphaName, Customers.Address1 =
lnk_LinkedCustomers.Address1, Customers.Address2 =
lnk_LinkedCustomers.Address2
WHERE Customers.AlphaName<>lnk_LinkedCustomers.AlphaName Or
Customers.Address1<>lnk_LinkedCustomers.Address1 Or
Customers.Address2<>lnk_LinkedCustomers.Address2 Or Customers.AlphaName Is
Null Or Customers.Address1 Is Null Or Customers.Address2 Is Null;

Any help would be appreciated..

 

[Michel Walsh]

Yes, you need an outer join, but it works only with Jet (not with MS SQL
Server)


UPDATE list RIGHT JOIN newPrice
ON list.ProductID = newPrice.ProductID
SET
list.ProductID = newPrice.ProductID,
list.UnitPrice = newPrice.UnitPrice,
list.Description = newPrice.Description



That AND updates existing records in table list, AND appends new records
(those in newPrice but not in list).



Hoping it may help,
Vanderghast, Access MVP