If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
decode
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? |
#2
|
|||
|
|||
decode
On Wed, 21 Feb 2007 14:36:05 -0800, Joshua6007
wrote: 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] |
#3
|
|||
|
|||
decode
Joshua6007 wrote:
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. -- Smartin |
#4
|
|||
|
|||
decode
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" wrote: Joshua6007 wrote: 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. -- Smartin |
#5
|
|||
|
|||
decode
Joshua6007 wrote:
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 (^: -- Smartin |
#6
|
|||
|
|||
decode
how do I code the choose command
"John W. Vinson" wrote: On Wed, 21 Feb 2007 14:36:05 -0800, Joshua6007 wrote: 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] |
Thread Tools | |
Display Modes | |
|
|