Updating a field in a table

[Norma.Caskey]

I am trying to figure out how to update a field in a table ONLY if it
matches a criteria.

In this example, I have a sales opportunity (with a unique
identifier). Each month we pull the sales report which has the
Opportunity # and the associated sales document number (quote, order
or invoice number).

I'd like to be able to import an Excel spreadsheet to append the table
with the latest sales document number. If the spreadsheet has the
same opportunity number and the sales document number has been
updated, I want to update that field in the table.

In theory it sounds easy but it completely escapes me!

Any help you can give me is greatly appreciated!

Norma

[chris.nebinger]

Easiest way is two steps: (well, 3 if you count setting up the
index).

Create a unique index on opportunity number to not allow more than one
number.
Then, Run an Update query to update the fields in the table from the
spreadsheet. This will only affect ones that are in both tables.
Next, run an Append query to add all records from the spreadsheet into
the table. Because it will try to append opportunity numbers that are
already there, some records will fail. The rest will be added.
And you're done!

Chris

On Feb 28, 2:03 pm, [e-mail] wrote:

I am trying to figure out how to update a field in a table ONLY if it
matches a criteria.

In this example, I have a sales opportunity (with a unique
identifier). Each month we pull the sales report which has the
Opportunity # and the associated sales document number (quote, order
or invoice number).

I'd like to be able to import an Excel spreadsheet to append the table
with the latest sales document number. If the spreadsheet has the
same opportunity number and the sales document number has been
updated, I want to update that field in the table.

In theory it sounds easy but it completely escapes me!

Any help you can give me is greatly appreciated!

Norma