append query overwrites all matching records
NoSpamTakeSquareRootOfNumber
another database on a click() event. How can I make that append query
overwrite all records where two fields match?
The button click will be in database A and will append to database B.
I want the query in database A to take all of its records, and if the
fields [Part ID] and [Revision] are the same in database B, then that
record will be overwritten with the new one. Otherwise, if there is no
record in database B where both [Part ID] and [Revision] are the same,
then create a new record.
It's a little complicated since it's happening in a separate database,
so I can't just open a query in database B to do comparisons. Any ideas?
John Vinson
<"nlee144(NoSpamTakeSquareRootOfNumber)"@eesus.jnj.com> wrote:
>I have an append query in a database that must append records into
>another database on a click() event. How can I make that append query
>overwrite all records where two fields match?
An Append query *adds new records which do not exist*. In order to
overwrite - update the values in - existing records, you need an
Update query instead; join the two tables, and update the fields in
TableB to the fields in TableA.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
NoSpamTakeSquareRootOfNumber
Will that also put the fields that do not exist into the older table?
Also, can I update into a separate database as I can with an append query?
John Vinson
<"nlee144(NoSpamTakeSquareRootOfNumber)"@eesus.jnj.com> wrote:
>Will that also put the fields that do not exist into the older table?
No. It will only update the fields that you tell it to update; it will
not change either table's structure, just the contents of the fields
referenced on the Update To line.
>Also, can I update into a separate database as I can with an append query?
Yes.
NoSpamTakeSquareRootOfNumber
> On Wed, 07 Jul 2004 09:01:29 -0400, NoSpamTakeSquareRootOfNumber
> <"nlee144(NoSpamTakeSquareRootOfNumber)"@eesus.jnj.com> wrote:
>
>
>>Will that also put the fields that do not exist into the older table?
>
>
> No. It will only update the fields that you tell it to update; it will
> not change either table's structure, just the contents of the fields
> referenced on the Update To line.
>
>
>>Also, can I update into a separate database as I can with an append query?
>
>
> Yes.
Do you know how I could get the update query to go into a second
database? I get an error when I try to form the SQL like the append
query, i.e.
"UPDATE tblSuppliers INTO tblSuppliers IN 'Database B.mdb'" gives me a
syntax error.
Secondly, since it won't add the new records, how can I make my append
query NOT add already existing records? I need to do something like
"WHERE (tblDatasheet.[Supplier ID] <> tblDatasheet.[Supplier ID] IN
'Database B.mdb')"
but I don't think I can compare fields in two databases.
Nathan