Re: Append query issues

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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



"Matt Beardsley" <MattBeardsley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0253A01C-C165-4C71-8BD4-9E4F6F0D9468@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... I have many many queries that check for orpaned data (for example, if there's a billing record with no matching custID in the customer table, I'm putting up a form with the unmatched data, and a quick way to update all unmatched to one archive type customer, etc. ... I was surprised to see duplicate entries in some of the static tables for dropdown selections, so I have to solve this one now. ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ...
    (comp.databases.ms-access)
  • Error from Append Query
    ... I am attempting to append some data fields to an existing table from a table ... Basically a customer information table ... The new table and the customer table share a common key ...
    (microsoft.public.access.queries)
  • Re: Access 2007 Append Query – Issue with automatically inserted b
    ... Create Table1 with one field called ?Customer Name? ... Create Query1 with ?Query Design? ... Choose Table2 for the ?Append To Table Name? ...
    (microsoft.public.access.queries)
  • Re: Filling a GRID with user-selected customer tables using same alias
    ... of a model customer table with no records. ... customertable to the view with APPEND FROM or fill it with INSERT INTO ... REQUERYto refresh the grid. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Searching for "append" program
    ... Thanks for the suggestion but I think you are misunderstanding what I am ... I want to take the contents of c:\logs\logs_2_2006\a.txt and append it to ... a.txt contains information about customer a, ...
    (microsoft.public.windows.server.general)