finding the highest value in different fields. (1 Viewer)

[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

 

[Pat Hartman]

FUGA -

but there is one right?

- no there isn't, nor will there ever be. Relational tables and spreadsheets are very different in their operations despite their similar look when tables are opened in datasheet view. There is no such thing as a cell and the intersection of a row and a column is not addressable as it is in a spreadsheet.

 

[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.

 

[Pat Hartman]

Would it be impossible to have Access "think" in this way?

- No, but you need to understand relational database theory to understand why you don't want Access to "think" like Excel.

Access is a relational database and as such adheres to the tenents of relational database theory just as Oracle, SQL Server, DB2, and many others do. In a RDBMS data is stored in tables. A table can be thought of as a single subject. The columns of a table are atributes that further define the subject. The rows in a table are called tuples and are identical in format to each other but contain the data related to a single instance of the subject. So if the Subject is Customer, each row of the Customer table contains information about a single, unique customer.

RDBMS' have all the expected aggregate and domain functions but they only work vertically. They do not work horizontally as they do in a spreadsheet, because each column of a table is a single attribute. Having functions that operate horizontally would imply that the RDBMS supports repeating groups and since data that repeats horizontally violates first normal form, no RDBMS would ever provide functions that operate horizontally. Moreover, if your process requires this, it is because your table is improperly defined. ie you have a repeating group that needs to be removed and stored properly as a separate table.

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.

 

[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.