Running an Update Query that updates multiple values in the same field

  • Thread starter Thread starter Chris Lucas
  • Start date Start date
C

Chris Lucas

Does anybody know how I can run an Update query that will
update multiple values in the same field?

Thanks!
Chris
 
Chris,

Not really sure what you mean, unless you want to update a field based
on some value in one or more other fields. If that is the case, you
could do something like:

Update yourTable
Set someField = IIF(NOT IsNull([fld1]), [fld1], IIF(NOT
IsNull([fld2]),[fld2], 'Both fld1 and fld2 are Null'))
FROM yourTable

--
HTH

Dale Fye


Does anybody know how I can run an Update query that will
update multiple values in the same field?

Thanks!
Chris
 
Hi Dale,
For example, I have a table that has a field in it called
code_camp which contains various codes for Penn State
campuses. Rather than typing out the name of each campus
they simply entered in UP for University Park, AA for
Altoona, etc. What I am trying to do is run an update
query that will automaticly change all the UPs to
University Park and all the AAs to Altoona, etc.

I am trying to run the update query by adding the field
code_camp 6 times to the Design Grid since there are 6
campuses that have abbreviations for them. This way I
could update all 6 at the same time. However, when I run
the query I get a message that says DUPLICATE OUTPUT
DESTINATION current_semester.code_camp

Thanks,
Chris
 
Simplest way would be to add a table with the abbreviation and the expansion and
then use that table joined to your original table whenever you need the expanded
campus name.

You could use the SWITCH function to do the update. I wouldn't do this unless
it was a one-time thing.

UPDATE yourTable
SET Code_Camp =
Switch(Code_Camp="UP","University Park",
Code_Camp ="AA","Altoona", ...)
WHERE Code_Camp in ("UP","AA",...)

Chris said:
Hi Dale,
For example, I have a table that has a field in it called
code_camp which contains various codes for Penn State
campuses. Rather than typing out the name of each campus
they simply entered in UP for University Park, AA for
Altoona, etc. What I am trying to do is run an update
query that will automaticly change all the UPs to
University Park and all the AAs to Altoona, etc.

I am trying to run the update query by adding the field
code_camp 6 times to the Design Grid since there are 6
campuses that have abbreviations for them. This way I
could update all 6 at the same time. However, when I run
the query I get a message that says DUPLICATE OUTPUT
DESTINATION current_semester.code_camp

Thanks,
Chris

-----Original Message-----
Chris,

Not really sure what you mean, unless you want to update a field based
on some value in one or more other fields. If that is the case, you
could do something like:

Update yourTable
Set someField = IIF(NOT IsNull([fld1]), [fld1], IIF(NOT
IsNull([fld2]),[fld2], 'Both fld1 and fld2 are Null'))
FROM yourTable

--
HTH

Dale Fye


Does anybody know how I can run an Update query that will
update multiple values in the same field?

Thanks!
Chris


.
Click to expand...
Click to expand...
 
Hi,


You can also make a table:


Translate ' table name
This IntoThat ' fields name
aa aaaaexpended
bb bbbbexpended ' data sample


Then, in a query, bring your original table and the table Translate. Join
the common field ( or the field to be translated, to [Translate].[This] ).
Change the SELECT type to an UPDATE query type. Drag the field to be updated
in the grid, and under it, at the line UpdateTo, type
[Translate].[IntoThat]


Execute the query (well, first, make a backup, or a copy, .... easier on the
nerves to make massive manipulations when we have a backup, in safety).


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top