Updating a field in a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Norma.Caskey@gmail.com - 28 Feb 2007 21:03 GMT

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

Reply to this Message

chris.nebinger@gmail.com - 28 Feb 2007 23:33 GMT

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, Norma.Cas...@gmail.com wrote:

> I am trying to figure out how to update a field in a table ONLY if it
> matches a criteria.
[quoted text clipped - 14 lines]
>
> Norma

Reply to this Message