Middle value of 3 columns, excluding zeroes (1 Viewer)

[jcuellar8]

I have three columns with numerical values. I need the middle value, unless one or more of them is zero. If one is zero, I need the mimum value of the remaining two, if two are zero, I need the remaining value, if all three are zero, I need zero. I've only written queries in design view, so I'd like to avoid SQL if possible. Dmin and Dmax wouldn't work as far as I can tell, becuase I have these zeroes that have to be factored in. I can think of a few long ways around, but would love to hear ideas on better solutions.

Here is the logic of the deal - if it helps.

(1) IF (Column 1 and Column 2 and Column 3 = 0, Then 0

If (Column 1 and Column 2 = 0, then Column 3
If (Column 2 and Column 3 = 0, then Column 1
If (Column 1 and Column 3 = 0, then Column 2

If Column 1 is zero and Column 2 and 3 are not zero, give me max of 2 and 3
If Column 2 is zero and Column 1 and 3 are not zero, give me max of 1 and 3
If Column 3 is zero and Column 1 and 2 are not zero, give me max of 1 and 2

For everything else,

(2) IF Column 1= Column 2 = Column 3 Then use any one of them

(2) IF Column 1 <> Column 2 <> Column

Exclude Max, Pick the next highest


If Column 1 >Column 2 and Column 1 > Column 3 AND Column 2 > 3 Then pick Column 2
If Column 1 >Column 2 and Column 1 > Column 3 AND Column 2 < 3 Then pick Column 3

If Column 2 >Column 3 and Column 2 > Column 1 AND Column 1 > 3 Then pick Column 1
If Column 2 >Column 3 and Column 2 > Column 2 AND Column 1 < 3 Then pick Column 3

If Column 3 >Column 2 and Column 3 > Column 1 AND Column 1 > 2 Then pick Column 1
If Column 3 >Column 2 and Column 3 > Column 1 AND Column 1 < 2 Then pick Column 2


(3) If Column 1=Column 2<>Column 3

If Column 1 and Column 2 is lower # (higher Column) than Column 3, then use Column 1 or 2

If Column 1 and Column 2 is higher # (lower Column) than Column 3, then use Column 1 or 2

(4) If Column 1 <> Column 2 = Column 3

If Column 1 is lower # (higher Column) than Column 2 or 3, then use Column 2 or 3

If Column 1 is higher # (lower Column) than Column 2 or 3, then use Column 2 or 3

(5) If Column 1 = Column 3 <>Column 2

If Column 1 or 3 is lower # (higher Column) than Column 2, then use Column 1 or 3

If Column 1 or 3 is higher # (lower Column) than Column 2, then use Column 1 or 3

(6) If Column 1 <> Column 3 = Column 2

If Column 2 or 3 is lower # (higher Column) than Column 1, then use Column 2 or 3

If Column 2 or 3 is higher #( lower Column) than Column 1, then use Column 2 or 3


If they are the same, pick one, If they are all different pick the middle value, Otherwise pick one of the duplicates

 

[DALeffler]

Your logic doesn't seem to be consistant.

I need the middle value, unless one or more of them is zero.

Which doesn't seem to follow, as far as I can see, with this:

If Column 1 is zero and Column 2 and 3 are not zero, give me max of 2 and 3
If Column 2 is zero and Column 1 and 3 are not zero, give me max of 1 and 3
If Column 3 is zero and Column 1 and 2 are not zero, give me max of 1 and 2

Is what you really were trying for this?

If Column 1 is zero and Column 2 and 3 are not zero, give me min of 2 and 3
etc...

If you pass the values of the 3 columns to a VBA function when the query is executed, you could sort ascending the 3 passed values and then pick the middle value. All the logic listed should then be inherent by sorting. Try this:

Function fncMidVal(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double

If dNum2 < dNum1 Then
    fncMidVal = dNum1
    dNum1 = dNum2
    dNum2 = fncMidVal
End If

If dNum3 < dNum2 Then
    fncMidVal = dNum2
    dNum2 = dNum3
    dNum3 = fncMidVal
End If

fncMidVal = dNum2

If dNum2 < dNum1 Then
    fncMidVal = dNum1
End If

End Function

This is a one-pass sort for 3 passed values that returns the middle value without regard for Nulls.

hth,

 

[jcuellar8]

Thank you. Your correction to my logic was right on.

We are testing the data now, but from a cursory review it looks to be doing exactly what we wanted. I'll let you know if we find any problem, but thank you so much!