Update query - syntax help

[Aaron Howe]

Help needed if possible, please!

I have two tables: YourInvoices and MyInvoices. Yours go
in first, I create them in the table using various pieces
of information you give me: the invoice number, the date,
your name, the invoice amount, etc. I then later on
invoice someone else (MyInvoices) using the date you
supplied me and your name. In other words:

YourInvoices.InvNum should update in MyInvoices.InvNum
as should
YourInvoices.Date update in MyInvoices.Date

So, it's a simple Append Query to add in the new records
from one table to the other.

However, each time I run the query and go through
the "Okay <click>, Okay <click>, Okay <click>" routine, I
get an error that some entries could not be updated/added
because they would be duplicates in the table. That's
fine, if they already exist in MyInvoices then they should
be ignored. I tried to create an expression to do that,
but it's failing. Here's the expression: [YourInvoices]!
[InvNum] «Expr» Not «Expr» = [MyInvoices]![InvNum]

I tried it without the «Expr» tags and it still didn't
work. How should this be written to ignore data which has
already been added? I tried making it an update query
instead and it had the same effect

 

[[MVP] S.Clark]

If you set a primary key or unique index on the destination table, then the
duplicate records will not be added.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting


Help needed if possible, please!

I have two tables: YourInvoices and MyInvoices. Yours go
in first, I create them in the table using various pieces
of information you give me: the invoice number, the date,
your name, the invoice amount, etc. I then later on
invoice someone else (MyInvoices) using the date you
supplied me and your name. In other words:

YourInvoices.InvNum should update in MyInvoices.InvNum
as should
YourInvoices.Date update in MyInvoices.Date

So, it's a simple Append Query to add in the new records
from one table to the other.

However, each time I run the query and go through
the "Okay <click>, Okay <click>, Okay <click>" routine, I
get an error that some entries could not be updated/added
because they would be duplicates in the table. That's
fine, if they already exist in MyInvoices then they should
be ignored. I tried to create an expression to do that,
but it's failing. Here's the expression: [YourInvoices]!
[InvNum] «Expr» Not «Expr» = [MyInvoices]![InvNum]

I tried it without the «Expr» tags and it still didn't
work. How should this be written to ignore data which has
already been added? I tried making it an update query
instead and it had the same effect