10 percent increase between fields formula

[Guest]

I have a query which contains a "Curr_Amount" column that has numbers, a
“Prev_Amount " column that has numbers and also a"Difference" column that is
blank. If the “Curr_Amount†increases or decreases from the “Prev_Amount†by
10% ONLY... I need to note it in the blank “Difference†column. I need help
with devising a formula to calculate if there is a 10 % increase or decrease…
but if the percentage is higher or lower than 10% I need the line in the
“Difference†column to be null. I hope I explained this correctly and I
appreciate any assistance.

 

[Smartin]

I have a query which contains a "Curr_Amount" column that has numbers, a
“Prev_Amount " column that has numbers and also a"Difference" column that is
blank. If the “Curr_Amount†increases or decreases from the “Prev_Amount†by
10% ONLY... I need to note it in the blank “Difference†column. I need help
with devising a formula to calculate if there is a 10 % increase or decrease…
but if the percentage is higher or lower than 10% I need the line in the
“Difference†column to be null. I hope I explained this correctly and I
appreciate any assistance.

Hello,

For starters, we could try nested IIf expressions to see if Curr is
equal to Prev +/- 10%:

Difference: IIf (Curr = Prev * 1.1, "10% increase",
IIf (Curr = Prev * 0.9, "10% decrease", Null),
Null)

The big caveat here is the decimal math may produce incorrect results.
We might be able to wrap some kind of tolerance around the math to
tighten it up. For this, it would help to know something about the
domain (double, single, integer...) and range (positive, negative, order
of magnitude) of the Curr/Prev values you expect to look at.

Maybe there are other ideas as well.

 

[Jamie Collins]

For starters, we could try nested IIf expressions to see if Curr is
equal to Prev +/- 10%:

Difference: IIf (Curr = Prev * 1.1, "10% increase",
IIf (Curr = Prev * 0.9, "10% decrease", Null),
Null)

The big caveat here is thedecimalmath may produce incorrect results.
We might be able to wrap some kind of tolerance around the math to
tighten it up. For this, it would help to know something about the
domain (double, single, integer...) and range (positive, negative, order
of magnitude) of the Curr/Prev values you expect to look at.

Agreed. Even if the values were CURRENCY, being the Access MVP's fixed
point decimal numeric of first choice, one has to be careful with data
typing. CURRENCY has a fixed decimal scale of four and the value 1.1
in SQL is of type DECIMAL, so multiplying a CURRENCY value by a native
decimal value of 1.1 can result in a DECIMAL value with a decimal
scale of five e.g.

SELECT CCUR(1.0135) * 1.1 AS decimal_result,
CCUR(1.0135) * CCUR(1.1) AS currency_result,

returns 1.11485 and 1.1148 respectively

Comparing a value of decimal scale five to a value (non-null) of type
CURRENCY will always return FALSE, so the OP probably wants to
preserve type when performing the equality test e.g.

SELECT CBOOL(CCUR(1.1148) = CCUR(1.0135) * 1.1) AS
decimal_comparison,
CBOOL(CCUR(1.0135) * CCUR(1.1)) AS currency_comparison

returns FALSE and TRUE respectively.

The rule of thumb is to operate on values using consistent types but
is not universally true e.g. division:

SELECT CCUR(1.0135) / CCUR(1.1) AS result_value,
TYPENAME(CCUR(1.0135) / CCUR(1.1)) AS result_type

returns 'Double'.

One reason the fixed point decimal numeric of *my* first choice is
DECIMAL is that it preserves type with division e.g.

SELECT SELECT TYPENAME(1.0135 / 1.1) AS result_type

returns 'Decimal'.

Jamie.

--