I have the following sample field & data in one table
MyValue Value1 Value2 Value3
100 20 30 90
200 null 210 150
My goals here is to divide myvalue with either value1 or value2 or value3
whichever is higher.
e.g in line one 100/90 and 200/210 in line two.
how to create the expression in my query
thanks
"zyus,"
If no negative values are possible, try:
IIf(Abs(Nz(Value1, 0) + Nz(Value2, 0) + Nz(Value3, 0)) = 0, Null,
MyValue / IIf(Nz(Value1, 0) > Nz(Value2, 0), IIf(Nz(Value1, 0) >
Nz(Value3, 0), Nz(Value1, 0), Nz(Value3, 0)), IIf(Nz(Value2, 0) >
Nz(Value3, 0), Nz(Value2, 0), Nz(Value3, 0))))
or by using IIf(X IS NULL, 0, X) instead of Nz(X, 0):
IIf(Abs(IIf(Value1 IS NULL, 0, Value1) + IIf(Value2 IS NULL, 0,
Value2) + IIf(Value3 IS NULL, 0, Value3)) = 0, Null, MyValue /
IIf(IIf(Value1 IS NULL, 0, Value1) > IIf(Value2 IS NULL, 0, Value2),
IIf(IIf(Value1 IS NULL, 0, Value1) > IIf(Value3 IS NULL, 0, Value3),
IIf(Value1 IS NULL, 0, Value1), IIf(Value3 IS NULL, 0, Value3)),
IIf(IIf(Value2 IS NULL, 0, Value2) > IIf(Value3 IS NULL, 0, Value3),
IIf(Value2 IS NULL, 0, Value2), IIf(Value3 IS NULL, 0, Value3))))
What if negative values are allowed, such as:
MyValue Value1 Value2 Value3
100 Null -20 -30
Do you want the largest absolute value or the largest actual value for
the division? If negative values are possible, then IsNull(Value1, 0)
used in a comparison to get the largest value might yield incorrect
results when only negative values exist. That makes the expression
more complex.
You'd still want to return a Null value if the three values are all
either Null or 0. The expression is already getting somewhat
complicated:
IIf(Abs(Nz(Value1, 0) + Nz(Value2, 0) + Nz(Value3, 0)) = 0, Null, X)
where X includes finding the greatest non-Null value out of the three
values -- not exactly short or trivial when negative values are
possible. At least you know that there exists some non-Null or non-
Zero value among the three fields once you get to the second part of
the IIf() function. Obtaining a proper SQL expression is possible,
but it could get as ugly as when Robert Trapp told Geni Wallace that
she had a nose like a bloodhound
.
If you still want to limit yourself to use just SQL to obtain a
solution, you should probably change the structure of your table:
MyValue ValueNum TheValue
100 1 20
100 2 30
100 3 90
200 1 Null
200 2 210
200 3 150
Then the Max() function can be used in a subquery without having to go
through all the Null logic by taking advantage of the fact that the
Max() function ignores Null values. Again, I'd say that the
expression you want, done properly, is complex enough to warrant
creating a module level public function if negative values are
allowed. In fact, even the expression for non-negative values is a
little long and not trivial to change.
James A. Fortune
(e-mail address removed)
RT: Geni, you should go under your home to find out where the gas leak
is. You've got a nose like a bloodhound.
GW: Are you calling me a b****?
RT: No, not at all. I meant like a huntin' dog.
GW: So now I'm goin' around huntin' for it too?
