decode

[Joshua6007]

Is there a function in Access that I can use in my queries to map values to
other values. (Oracle's decode) In other words, if I have a table with
payment frequency that contains 12 (monthly), 4(quarterly), I would like to
translate those values to some factor, like 12 corresponds to 0.5, while 4
corresponds to 2 which is then multiplied with another field. How would I do
this in my query?

 

[John W. Vinson]

Is there a function in Access that I can use in my queries to map values to
other values. (Oracle's decode) In other words, if I have a table with
payment frequency that contains 12 (monthly), 4(quarterly), I would like to
translate those values to some factor, like 12 corresponds to 0.5, while 4
corresponds to 2 which is then multiplied with another field. How would I do
this in my query?

Two or three choices:

- If you have an integer and want a conversion for each value from 1
to some reasonable n, use Choose:

Choose([frequency], <value for 1>, <value for 2>, <value for 3>,
<value for 4> etc.)

- For a small number of arbitrary values use the Switch() function. It
takes arguments in pairs, evaluates them left to right, and returns
the second member of the first pair with TRUE as the value of the
first member:

Switch([frequency] = 12, 0.5, [frequency] = 4, 2, True, NULL)

- Most flexibly, easiest to maintain, but the most work to set up:
create a small translation table with a field for the frequency and a
field for the factor. Include this table in the Query you're using to
do the calculation, joining on the frequency field.

John W. Vinson [MVP]

 

[Smartin]

Is there a function in Access that I can use in my queries to map values to
other values. (Oracle's decode) In other words, if I have a table with
payment frequency that contains 12 (monthly), 4(quarterly), I would like to
translate those values to some factor, like 12 corresponds to 0.5, while 4
corresponds to 2 which is then multiplied with another field. How would I do
this in my query?

Nested IIf statements should do it, but the structure is a bit different
from Decode. E.g.,

IIf (Freq, 12, 0.5, IIf (Freq, 4, 2, <default value>))

If you have a lot of possible values IIf can get rather messy, and you
may want to set up a crosswalk/translation table instead.

 

[Joshua6007]

Thanks, there is really no need to set up a lookup table because this
information is never going to change - monthly will always be 12, quarterly
will always be 4, etc.

 

[Smartin]

Thanks, there is really no need to set up a lookup table because this
information is never going to change - monthly will always be 12, quarterly
will always be 4, etc.

That's cool... I think John Vinson's SWITCH solution was far more
elegant anyway. Plus, it's much more like DECODE (^:

 

[Ed S]

how do I code the choose command

Is there a function in Access that I can use in my queries to map values to
other values. (Oracle's decode) In other words, if I have a table with
payment frequency that contains 12 (monthly), 4(quarterly), I would like to
translate those values to some factor, like 12 corresponds to 0.5, while 4
corresponds to 2 which is then multiplied with another field. How would I do
this in my query?

Two or three choices:

- If you have an integer and want a conversion for each value from 1
to some reasonable n, use Choose:

Choose([frequency], <value for 1>, <value for 2>, <value for 3>,
<value for 4> etc.)

- For a small number of arbitrary values use the Switch() function. It
takes arguments in pairs, evaluates them left to right, and returns
the second member of the first pair with TRUE as the value of the
first member:

Switch([frequency] = 12, 0.5, [frequency] = 4, 2, True, NULL)

- Most flexibly, easiest to maintain, but the most work to set up:
create a small translation table with a field for the frequency and a
field for the factor. Include this table in the Query you're using to
do the calculation, joining on the frequency field.

John W. Vinson [MVP]

 

[John W. Vinson]

how do I code the choose command

Open the VBA editor (type Ctrl-G); press F1 for online help; find Choose. It's
all there.

Short answer: the first argument to Choose accepts an integer; one of the
remaining arguments will be returned, based on the integer. For example,

Choose(3, "A", "B", "C", "D")

will return C - the third argument in the list.

John W. Vinson [MVP]