Append query issues

[Guest]

Hello,

I am trying to append some data from a query to a table, but am running into
some issues. Specifically I am entering delivery orders into a form which is
updating a table (lets say "delivery") which also uses some data from my main
"customer" table to fill in the gaps (to cut down on info needing to be typed
in).

What I am trying to do with this data is add records from my "delivery"
table to my "customer" table that do not already exist by verifying the
address (ie if I enter an order into "delivery" table for 123 Main St, and
this address is not in the main "customer" table, I want to add a record with
this info so that it creates a new customer at 123 Main St). What I don't
want to do is create multiple new customers with the same address, otherwise
I could just append all records form "delivery" to "customer"?

Any ideas on how to accomplish this? To complex for one append query? Run
a query that only finds delivey records that do not match addresses in
customer table and then append the records? How would I do this?

If any other info is needed let me know, and thank you in advance!

Matt

 

[Michel Walsh]

Hi,



To update unit price of items in table Inventory with the new prices from
table NewItems, AND to append brand new items mentioned in NewItems but that
were never seen before in Inventory, someone can use:


UPDATE NewItems LEFT JOIN Inventory ON NewItems.ItemID = Inventory.ItemID
SET Inventory.ItemID=NewItems.ItemID,
Inventory.UnitPrice = NewItems.UnitPrice,
Inventory.ItemDescription = NewItems.ItemDescription



That works only with Jet, not with MS SQL Server.

Hoping it may help,
Vanderghast, Access MVP