Multiple Update values in one query

[Guest]

Can I use multiple values in one update query to update one field based on a
value in the field. I have a field that can have 5 possible codes (0,1,2,3,8)
and I would like to run one update query (I know I can write 5 different, but
hope can do in one) to change these values to their actual device
equivalants, for easier to read. If value = 0 then Pollcat II, value 1
Pollcat III, value 2 Netlink, value 3 CC3, value 8 FTP. Is there a way to
do a Case type statement in an update query?

Thanks in advance

Kenny A.

 

[Chris L.]

Can I use multiple values in one update query to update one field based on a
value in the field. I have a field that can have 5 possible codes (0,1,2,3,8)
and I would like to run one update query (I know I can write 5 different, but
hope can do in one) to change these values to their actual device
equivalants, for easier to read. If value = 0 then Pollcat II, value 1
Pollcat III, value 2 Netlink, value 3 CC3, value 8 FTP. Is there a way to
do a Case type statement in an update query?

Thanks in advance

Kenny A.

One way would be creating a translator table with columns DEVICE_ID
and DESCRIP for example. Then storing "0", "Pollcat II" and so on.
Then joining translator table to updated table in your update query
using column DEVICE_ID.

Although a "case statement" would work for a 5-values scenario, think
about what would happen if instead of 5 possible values there could be
5000 of them. All you would have to do is insert the 5000 records into
the translator table.

 

[Guest]

Chris,
Although your way will work, is there a way within the query itself to do
a case or if statements (maybe IIF statements)? There are only the 5 types I
described, if anything, maybe one or two more could be added max. I know I
could do 5 easy update queries within a macro (one for each value), but tryig
to do within one query. Is it possible to do via case or IIF statements (or
other conditional form)?

Thanks
Kenny A.

 

[Chris L.]

Chris,
Although your way will work, is there a way within the query itself to do
a case or if statements (maybe IIF statements)? There are only the 5 types I
described, if anything, maybe one or two more could be added max. I know I
could do 5 easy update queries within a macro (one for each value), but tryig
to do within one query. Is it possible to do via case or IIF statements (or
other conditional form)?

Thanks
Kenny A.







- Show quoted text -

Nested IIF statements should do it.

iif (value=0, "Pollcat II", iif (value=1, "Pollcat III", iif
( ...etc... )))