Problems with Dmax-Dmin 1

[microtoolmaker]

I have 3 fields in a query that hold like numerical data. I would like to find the difference between the highest number and the lowest from these 3 fields in each record.<br>
In a spreadsheet this is easy(@max-@min)...column or row made no difference.<br>
<br>
I can use Dmax and Dmin within fields but seemingly not accros fields (to created a calculated field)....is this true? is there a way around this?

 

[Lehtoant]

Sounds tricky and my solution helps you only to find the right results (integrating them to form or report is tricky as well)<br>
<br>
Make two nested SQL queries, first one flips your three fields as rows and the second calculates the difference (like in Excel). I'm assuming that you have some kind of ID field.<br>
<br>
Query1:<br>
select YourRowID, 1 as FieldNum, YourField1 as FieldValue from YourTable<br>
UNION ALL <br>
select YourRowID, 2 as FieldNum, YourField2 as FieldValue from YourTable <br>
UNION ALL <br>
select YourRowID, 3 as FieldNum, YourField3 as FieldValue from YourTable<br>
ORDER BY RowID, FieldNum<br>
<br>
Query2:<br>
Select RowID, Max([FieldValue])-Min([FieldValue]) AS MaxDiff from Query1<br>
GROUP BY RowID<br>
<br>
Good luck,<br>
Al

 

[DougP]

How about putting this in your query in the field area<br>
Put the whole thing &quot;Expr1:&quot; and all<br>
Expr1: Max(value)-Min(value)<br>
Where value is the name of your field<br>
<br>
I tested it and it does work<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[JimConrad]

Let me see if I understand your question...<br>
<br>
You have a query with three columns (ColA, ColB, ColC) and you want to find the highest value of the three columns and the lowest value and then the difference between them.<br>
<br>
So, for example...<br>
<br>
Row 1: ColA=10, ColB=5, ColC=12. Diff=12-5=7 <br>
Row 2: ColA=3, ColB=17, ColC=3. Diff=17-3=14<br>
<br>
If this correct? <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[microtoolmaker]

Exactly....<br>
This is the "range" in a sample of 3 individual measurements for an Xbar&R chart.

 

[JimConrad]

Create a module and copy this code:<br>
<br>
Function MaxOfThreeValues(valueA As Variant, valueB As Variant, valueC As Variant) As Variant<br>
<br>
Dim varMax As Variant<br>
<br>
varMax = valueA<br>
<br>
If IsNull(varMax) Then varMax = valueB<br>
If IsNull(varMax) Then varMax = valueC<br>
<br>
<br>
If IsNull(valueB) Then<br>
' continue<br>
ElseIf valueB &gt; varMax Then<br>
varMax = valueB<br>
End If<br>
<br>
If IsNull(valueC) Then<br>
' continue<br>
ElseIf valueC &gt; varMax Then<br>
varMax = valueC<br>
End If<br>
<br>
MaxOfThreeValues = varMax<br>
<br>
End Function<br>
<br>
<br>
Function MinOfThreeValues(valueA As Variant, valueB As Variant, valueC As Variant) As Variant<br>
<br>
Dim varMin As Variant<br>
<br>
varMin = valueA<br>
<br>
If IsNull(varMin) Then varMin = valueB<br>
If IsNull(varMin) Then varMin = valueC<br>
<br>
<br>
If IsNull(valueB) Then<br>
' continue<br>
ElseIf valueB &lt; varMin Then<br>
varMin = valueB<br>
End If<br>
<br>
If IsNull(valueC) Then<br>
' continue<br>
ElseIf valueC &lt; varMin Then<br>
varMin = valueC<br>
End If<br>
<br>
MinOfThreeValues = varMin<br>
<br>
End Function<br>
<br>
<br>
Now you can create new columns in your query...<br>
<br>
MaxField: MaxOfThreeValues(Field1,Field2,Field3)<br>
MinField: MinOfThreeValues(Field1,Field2,Field3)<br>
Diff:MaxField - MinField<br>
<p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[microtoolmaker]

WORKS PERFECT!<br>
Thank you very much ! <br>
dcb