finding the highest value in different fields.

[Fuga]

Hi.

I have a query with three calculated fields. I want to take the highest value from those fields and use in another calculation.
(Like max(a1:a3) in excel).

I can´t seem to find a function for this, but there is one right?
If I should use dmax or max in some way please help me with the syntax. I looked in Access help but I couldn´t figure it out.


Fuga.

 

[dcx693]

Surprise, there isn't a Max() function in Access. At least, not the way there is in Excel. The Max() function that Access supports is a SQL aggregate function as in "SELECT Max([MyField]) As MaxMyField FROM myTable".

So what can you do? With 3 values, you're best off writing yourself a simple custom function. Pass all three values to it, and let it return the maximum using very simple logic like: 1st value =x, if 2nd value > x, then x=2nd value. If 3rd value > x, then x = 3rd value. Return x.

 

[DJN]

Here is a function that will give the maximum value of a set of data.

Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim i As Integer
Dim currentVal As Variant

' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)

' Cycle through each value from the row to find the largest.

For i = 0 To UBound(FieldArray)
If FieldArray(i) > currentVal Then
currentVal = FieldArray(i)
End If
Next i

' Return the maximum value found.
Maximum = currentVal

End Function

SELECT tblMinMax.Field1, tblMinMax.Field2, tblMinMax.Field3, minimum([Field1],[Field2],[Field3]) AS [Minimum Value], maximum([Field1],[Field2],[Field3]) AS [Maximum Value], [Maximum Value]-[Minimum Value] AS Range
FROM tblMinMax;

Change you table and field names as required.

David

 

[Len Boorman]

Alternatively query to find max of each field, then UNION and then Max of UNION

Round the houses but it gets there

L

 

[Fuga]

Thank you all for your help.

So I guess it´s no wonder I didn´t figure it out.

I will try your suggestions and see how they work. It is supposed to run on a table with some 500 000 posts so it can´t be too slow.

How about the IIF? Slow, right?

Pat Hartman
Is there a reason why Access and Excel behave differently in this respect? Would it be impossible to have Access "think" in this way? I know there are no spreadsheets and cells in Access, but would it be impossible to have similar features? I´m asking because I´ve read a lot of your posts and you always seem to know the answer to these questions. If you - or anyone else for that matter - could explain it to me that would be great.

Fuga.

 

[Fuga]

Do some reading on relational database design. There are lots of references here. Be sure to search the MSDN library and the MS kb also.

I will.

Thank you for taking the time to answer.

Fuga.