Update query updates multiple times

[Guest]

I have an update query which looks at a field in the imported data table, and
if this value is less than the corresponding value in the lookup table,
updates a field in the imported data table to designate this. However, if I
have more than one value for the same account in the lookup table, or in the
original table, the update query updates the field with multiple instances of
the identifier. I'm doing this all in design view... but would it be best to
do it in SQL? The main table has over 30 fields, so I'll list the related
fields between the two only:

Lookup Table -> Table To Update:

Customer -> CUSTOMER NAME
Document No -> Invoice Number
Cell No -> MOBILE #
ESN -> IMEI
Total -> AMOUNT

The remaining three fields in the lookup table have no relation to anything
else in the imported data table.

The query looks like this:

UPDATE [Incomplete Match Table] INNER JOIN [Invoice Lookup Table] ON
[Incomplete Match Table].IMEI = [Invoice Lookup Table].ESN SET [Incomplete
Match Table].[Updated By] = [Updated By] & " - LTA"
WHERE ((([Incomplete Match Table].[Updated By])="ESN Number - Incomplete
Match") AND (([Incomplete Match Table].[Invoice Number])=Left([invoice lookup
table].[document no],7)) AND (([Incomplete Match Table].AMOUNT)<[Invoice
Lookup Table].[Total] And ([Incomplete Match Table].AMOUNT)>0));

The output I am trying to get is "ESN Number - Incomplete Match - LTA", but
what I'm getting is multiple instances of the " - LTA" tag: "ESN Number -
Incomplete Match - LTA - LTA - LTA".


Any ideas?

 

[Guest]

I've also tried writing a select query to pull the first instance of each
invoice/dollar amount combination only and then use that as a lookup table to
update the main table, but I keep getting the error message "Operation must
use an updateable query."