Literal values in calulated expressions

  • Thread starter Thread starter jabel
  • Start date Start date
J

jabel

I am trying to prevent Access accuracy from providng me
with a quotient value that is made up of background
calulated variables:
i.e. I need the literal values to be used for the quotient
calculation.

e.g. EXPR1:(0.25*[EXPR2])+[EXPR3], then using EXPR1:
EXPR5=[EXPR1]/[EXPR4] where EXPR4 is also a derived
expression. I need the literal values, say 1509.63 and
2390.49 to be used that are the literal results of ExPR1
and EXPR4.
 
If you are trying to do this in a single query, you will have to spell
each Expression explicitly. You cannot refer to a computed column as
a parameter in another column in a single query. You can, however,
reenter the equation that gave you that value.

If Expr2 is defined as A + B, and Expr3 is defined as A*B then
you would replace Expr1: = (0.25 * [Expr2]) + Expr3
with Expr1: = (0.25 * (A+B)) + (A*B)

Or, you could create one query that defines a set of the expressions
that do not refer to any other computed columns, then create a second
query that uses the computed columns from the first query to get those
expressions.

Or, another way would be to create functions that do all of the
intermediate calculations. Then all you would need to do is pass the
values to the functions and use those. My guess is that this would be
slower than the 2nd option, since that option would compute all of the
first set of Expressions and could be reused without having to
recalculate their values. Using a function would require that the
function be reevaluated each time it is used, so if you use Expr2, 4
times in subsequent expressions, then it would have to be computed 4
times.

Public fnExpr2(A as variant, B as Variant) as Variant
fnExpr2 = A + B
End function

Public fnExpr3(A as Variant, B as Variant) as Variant
fnExpr3 = A * B
End function

SELECT (0.25 * fnExpr2([field1], [field2])) + fnExpr3([field4],
[field5]) as Expr1


--
HTH

Dale Fye


I am trying to prevent Access accuracy from providng me
with a quotient value that is made up of background
calulated variables:
i.e. I need the literal values to be used for the quotient
calculation.

e.g. EXPR1:(0.25*[EXPR2])+[EXPR3], then using EXPR1:
EXPR5=[EXPR1]/[EXPR4] where EXPR4 is also a derived
expression. I need the literal values, say 1509.63 and
2390.49 to be used that are the literal results of ExPR1
and EXPR4.
 
Jabel

I am unfamiliar with your use of the expression "background calculated
variables", and am not certain how Access "accuracy" (is this the same as
precision, or number of decimal places?) is related.

What is the precision (?accuracy) of the underlying expressions -- you've
given a couple examples of numbers that may have only been rounded/displayed
to two decimal places. Are you working with doubles, singles, decimals, or
currency data types?

More info, please...

Jeff Boyce
<Access MVP>
 
Yes, I mean that if you have complex calculations that use
derived variables, such that, C=A*B, where A is an input
value but B is derived by say D*F which may themselves be
inputs or derived, then G=B/H, where H could be an input
value or a derived expression such as M+N..I beilve that
in a single query you have to enter all expressions
explicitly and cannot use computed columns as a parameter
in another column, this can get, in my case, very
protracted with huge expressions.

I have looked at the FieldSize setting in the source
tables. The variables Iam using have specific decimal
place properties such that one input variable, say price,
will need to be input to 6 decimals, while another will be
input at 2 decimals. This is required by the demands of
the data. I was using Double but considered the Precision
to be too much i.e., 15 and Iam currently using Decoimal
and setting the Precison, Scale and Decimal Places as the
input variable demands (all this in the Table Filed
properties).

As an example, two of my calculated values are say 2390.49
and 1506.09 then dividing 1506.09/2390.49 should result
(at 8 decimal places) 0.63003401 but both these numerator
and denominator are derived and the query gives me
0.63003313 and this result is used to calculate a later
value and the inaccuracy or "overaccuracy" carries
through.

In other words I need the literal results to be divided,
as displayed. Some values do not become affected and there
result is consistent with the actual literal values that
are used to calculate them. Quotients seem to cause a
particular problem. It is as if I need the "Precision as
Displayed" setting that is available in Excel that will
use the literal values and the decimals you set.

I hope this clarifies the situation. I would appreciate
any input regarding this issue.
Jabel
-----Original Message-----
Jabel

I am unfamiliar with your use of the
Click to expand...
expression "background calculated
 
Thanks for the input.

I have broken every expression into the fully expressed
forms such that only data values are or raw data is used
throughout the calcs. I even changed the IIF functions to
be explicit, rather than EXPRX:IIF([REVENGAS]>0,[TRUE],
[FALSE]) I opened up [REVENGAS] to be fully delclared as
EXPR:IIF(([INT]*[TSALEGAS])*[PRICEGAS]>0,[TRUE],[FALSE])
as well as anything that might also be in the [TRUE] or
[FALSE]. There is one variable that will not calculate
literally. It is a quotient but I have fully expressed all
the parameters within this expression. I might try your
other suggestion of creating a query that contains all the
expressions based on non-computed columns and then use
this in a second query to compute my needed values.

Thanks again.

Jabel
-----Original Message-----
If you are trying to do this in a single query, you will have to spell
each Expression explicitly. You cannot refer to a computed column as
a parameter in another column in a single query. You can, however,
reenter the equation that gave you that value.

If Expr2 is defined as A + B, and Expr3 is defined as A*B then
you would replace Expr1: = (0.25 * [Expr2]) + Expr3
with Expr1: = (0.25 * (A+B)) + (A*B)

Or, you could create one query that defines a set of the expressions
that do not refer to any other computed columns, then create a second
query that uses the computed columns from the first query to get those
expressions.

Or, another way would be to create functions that do all of the
intermediate calculations. Then all you would need to do is pass the
values to the functions and use those. My guess is that this would be
slower than the 2nd option, since that option would compute all of the
first set of Expressions and could be reused without having to
recalculate their values. Using a function would require that the
function be reevaluated each time it is used, so if you use Expr2, 4
times in subsequent expressions, then it would have to be computed 4
times.

Public fnExpr2(A as variant, B as Variant) as Variant
fnExpr2 = A + B
End function

Public fnExpr3(A as Variant, B as Variant) as Variant
fnExpr3 = A * B
End function

SELECT (0.25 * fnExpr2([field1], [field2])) + fnExpr3 ([field4],
[field5]) as Expr1


--
HTH

Dale Fye


I am trying to prevent Access accuracy from providng me
with a quotient value that is made up of background
calulated variables:
i.e. I need the literal values to be used for the quotient
calculation.

e.g. EXPR1:(0.25*[EXPR2])+[EXPR3], then using EXPR1:
EXPR5=[EXPR1]/[EXPR4] where EXPR4 is also a derived
expression. I need the literal values, say 1509.63 and
2390.49 to be used that are the literal results of ExPR1
and EXPR4.


.
Click to expand...
 
If you know what the literals are suppose to be you could use the round
function. If A=B*C and D=E*F both of which you want returned to 2 decimal
places. Use A=round(B*C,2), D=round(E*F,2). Then using G=A/D should give
you the correct number.

Kelvin
 
I thinkk I was looking too far and too deep for a
solution. I was able to overcome the problem with the use
of the ROUND function; so that EXPRC:ROUND([EXPRA],2)/ROUND
([EXPRB],2). EXPRA is a computed value as is EXPRB. In
other words round the values that are computed from
another column before completing the operation, e.g.
division. This of course forces the desired decimal
placing for the values the numerator and denominator in
the calculation rather than relying upon the values as
supplied directly from the computation in another column.

Thanks again for the input and discusion op on this.
Jabel
 
Thanks, Kelvin, It suddenly came to me and I did what you
had suggested. By rounding the numerator and deniminator
the resulting quotient gives me the correctly rounded
number. I was looking too deep and too far with issues
about FieldSixe etc..at least for this situation.

Jeff
 
Back
Top