How can I run a update and append at the same time.?

[Michael]

I want to import data from a txt file. some records have been updated. If I
run a update query will it append the new records?
thanks
Michael

[John Vinson]

It depends on how your table structures are set up. It will import the
entire text file; if you *just* import it it will create a new
imported table; if you import it to an existing table it will append
all records; if there is a Primary Key in the table, and it's one of
the fields being imported, records which would violate the primary key
constraint will NOT be imported nor will they be updated, you'll just
get key violation errors.

You'll have to decide how you want to handle this, and write specific
Queries to handle it the way you want. A simple File... Get External
Data... Import probably will not suffice!

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

[Michael]

Sorry john... I did not make myself clear. I am importing from a txt file..
but that is all handled. What I need to do actually is to update the various
tables once I have the data imported. ..hmm I am still not being very clear.
forget about the import.

I have a table with some new records and some records that are already in
the system but have been updated. (for example and order that has had
products added or removed). So I need to take all the data that applies to
the orders table and either append the new records and also update the
existing data. I am using a append query now and I have my primary key set
to the order number.. so any of the corrected data does not get updated.
still confusing. but the best I can do.. too many hours on the computer..
gotta get a life.. thanks
Michael

[Joe Fallon]

Here's a way to use a single query to do both Update and Append at the same
time.

In the query grid add 2 tables: OldData and NewData.
Join the key field(s).
Change the join to a left join going from NewData to OldData.
Select all fields from OldData.
Change Query to Update query.
Update to: [NewData]![fieldname]
Perform the above step for all fields.
(Kind of a pain, but you only have to create the query once.)
Change Query Property: Unique Records = No

This should update records if the key exists and append records when it
doesn't.

--
Joe Fallon
Access MVP

"Michael" <Michael...@hotmail.com> wrote in message
news:#1kq4pgGCHA.2672@tkmsftngp13...

[John Vinson]

On Sat, 22 Jun 2002 21:29:48 +0200, "Michael"
<Michael...@hotmail.com> wrote:

>So I need to take all the data that applies to
>the orders table and either append the new records and also update the
>existing data. I am using a append query now and I have my primary key set
>to the order number.. so any of the corrected data does not get updated.

You will need both an Append query and an Update query. The Append you
evidently have in hand; the Update query should join the main table to
the imported table by the Primary Key and update each field to
[Importtable].[Fieldname]. You can run both queries from VBA code or
from a macro.

There is a trick I've seen but not used, updating a Left Outer Join
query to update the right-hand table. Old records will be updated,
nonexistant records will be NULL and therefore will be added... but I
can't remember just how it works this time of night!

[Michael]

[david @ epsom dot com dot au]

If you use an update query instead of an append query, it will
update all the records, including the ones that aren't there...If
it can work out which records to update.

To do this you need to do a join from your update table to your
data table, so that you get ALL records in your update table,
and those records in the data table that match. If you run that
as a select query, you will see all the 'missing' records show up
as blank. If you run it as an update query on the data table, you
will see that all those 'blank/missing' records get updated to the
values from the update table.

(david)

"Michael" <Michael...@hotmail.com> wrote in message news:Oy3OXMiGCHA.2520@tkmsftngp13...