Updating multiple values using a query

[Guest]

How can I update multiple values in the same field using an update query?
For instance:

Field1
Update to "1"
Criteria: "A"

Field1
Update to "2"
Criteria: "B"

and so on...

 

[Jeff Boyce]

?!Your field stores more than one value in the same row/field?! This is not
advisable in a well-normalized data structure.

If you have a one-to-many relationship (i.e., you need to store zero, one or
more values), add a one-to-many "child" table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You can use an expression with nested IIF function calls e.g. to update the
value if criteria A or B are met but leave the value unchanged otherwise:

UPDATE YourTable
SET Field1 = IIF(<criterion A>, 1, IIF(,<criterion B>, 2, Field1));

where <criterion A> and ,<criterion B> are expressions evaluating to TRUE or
FALSE.

However, with a large number of criteria this gets very unwieldy and it
would be better to write a VBA function into which values could be passed as
arguments, and which returns the value to which the field is to be updated.

Be sure to back up the table before undertaking any large scale update
operations like this!

Ken Sheridan
Stafford, England

 

[Guest]

No, that's not the case. Let's say Field1 can contain the following values
1, 2, 3. In this simplified example, I want to run an update query to change
the value 1 to A, 2 to B, and 3 to C in my table. Can this be accomplished?

 

[Jeff Boyce]

If this is a one-time conversion thing, Ken's reply (using IIF()) will do
it.

If this is a repeat, ongoing situation, is there a way you could put the
"A", "B", "C" in the field to start with?

By the way, your field will need to be of type "text" to take the
alphanumeric characters (but the original 1, 2, 3 could be integer data
type...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[paul]

The simplest way I can think of to do this, is to create a lookup table with
CURRENT_VALUE and NEW_VALUE.

Then you link the field you want to update to the CURRENT_VALUE field in
your lookup table, and you build the field that says UPDATE TO: to be the
value in the lookup tables NEW VALUE field (for example
[TBL_LOOKUP]![NEW_VALUE] )

That way, you dont have to do any coding, and you can update a potentially
unlimited amount of options.

You can also save it incase you ever want to update the table again if a new
change comes along.

Paul