Max of multiple columns

[Poppa Smurf]

Khalil

Using part of the code your minimum function as per previous post.

' Cycle through each value from the row to find the smallest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I

I want to find the minimum number except zero and maximum number

To exclude 0 from the test we need to add an IF END IF statement to check for a zero in the FieldArray. This is done by adding IF FieldArray(I) > 0 then and END IF as ahown in the code below.

If the value in the FieldArray(I) is greater than 0 then the next 3 lines code are processed.
If the value in the FieldArray(I) is equal to 0 then the next thrre lines are not processed.
For I = 0 To UBound(FieldArray)
IF FieldArray(I) > 0 then
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
END IF
Next I

 

[Khalil Islamzada]

Dear friend Poppa Smurf:

Thank u so much it works, very well.



Khalil

Khalil

Using part of the code your minimum function as per previous post.

' Cycle through each value from the row to find the smallest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I



To exclude 0 from the test we need to add an IF END IF statement to check for a zero in the FieldArray. This is done by adding IF FieldArray(I) > 0 then and END IF as ahown in the code below.

If the value in the FieldArray(I) is greater than 0 then the next 3 lines code are processed.
If the value in the FieldArray(I) is equal to 0 then the next thrre lines are not processed.
For I = 0 To UBound(FieldArray)
IF FieldArray(I) > 0 then
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
END IF
Next I

 

[natashaP]

Hi Poppa Smurf

I'm still getting "0" after using the suggested code
To exclude 0 from the test we need to add an IF END IF statement to check for a zero in the FieldArray. This is done by adding IF FieldArray(I) > 0 then and END IF as ahown in the code below.

If the value in the FieldArray(I) is greater than 0 then the next 3 lines code are processed.
If the value in the FieldArray(I) is equal to 0 then the next thrre lines are not processed.
For I = 0 To UBound(FieldArray)
IF FieldArray(I) > 0 then
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
END IF
Next I

Please help . Thank you !

 

[Poppa Smurf]

Hello Natasha

You will get a code if you have a record with a value of 0 in your data OR the currentVal is not set. See an earlier message where the currentval is set in the code.

I have attached a sample database that will give a minimum value.

If you are still having problems please post a sample database with you code to the forum.

 

[sanket91000]

Thanks for the code that you sent in here. That works for me too. I just want to go one step ahead and display the column name for that maximum field. Can you please show me how to do that?

Thank in Advance !!!

 

[Galaxiom]

I just want to go one step ahead and display the column name for that maximum field.

Testing values in different fields against each other strongly suggests an inappropriate data structure. Wanting to know the name of the field with the maximum value confirms it.

These values should be stored in a related table with one record for each value. Another field stores a value to indicate which field the data would have been stored in using the current structure.

It is then a simple matter to find the maximum and display the name in the other field.

 

[cigarprofiler]

This is very handy. I applied it to a query on a crosstab query, which enabled me to find the mode of each row (the column containing the highest number).

 

[jukos]

This solution worked beautifully for me. Thank you!

 

[Tezza444]

Hi

I want to do the same but when I run the query I get undefined function Maximum.
Have been using Access for a while but very new to coding.

Tezza444

 

[jukos]

You need to add the "Maximum" function in a module.

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

 

[The_Doc_Man]

At different times during this thread that is now pushing 10 years old, both Galaxiom and I have commented on a simple fact. IF it is meaningful for you to compare fields in the same record in this way, you have a data structure that is not normalized.

Excel can surely do this, but Access is designed differently which is why you have to go to the trouble of creating a function for it. This kind of activity is NOT NATIVE to Access. As such, you can expect it to be more difficult.

 

[Tezza444]

Thanks Jukos

I have done that. see below


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()

' 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

Is copied into the module and the query created has the following expression in

T1Max: Maximum([T1],[T2],[T3],[T4],[T5],[T6],[T7],[T8],[T9],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20])

I have probably made a rookie mistake, but I am a rookie!

Tezza444

 

[Galaxiom]

I have probably made a rookie mistake, but I am a rookie!

Yes. You have continued to develop your database with an inappropriate data structure that belongs in a spreadsheet rather than a database.

You will continue to require clumsy, inefficient workarounds until you understand database normalization.

 

[Tezza444]

Thanks for the advice. I am new to this and though I appreciate your comment it did zero to help

 

[Gasman]

If you have to stick your head in the sand then try

currentVal = FieldArray(0)

For I = 1 To UBound(FieldArray)
    If FieldArray(I) > currentVal Then
        currentVal = FieldArray(I)
    End If
Next I

 

[Tezza444]

Thanks Gasman. Again another comment unexplained.
I am not sticking my head in the sand I am learning.
I can play the saxophone and knit an arran sweater with ease but I am new to database code.
If you are going to make comments explain what you mean or don't bother commenting!!!!

 

[Tezza444]

Yes. You have continued to develop your database with an inappropriate data structure that belongs in a spreadsheet rather than a database.

You will continue to require clumsy, inefficient workarounds until you understand database normalization.

Explain what you mean instead of trying to sound clever!!!!

 

[Tezza444]

Oh! A bitch! I like taking down bitches.

I just hope you don't lose your Balls and flounce away.

Sent from Newbury UK

You sad individual. No help just being a cockwomble

 

[Gasman]

Thanks Gasman. Again another comment unexplained.
I am not sticking my head in the sand I am learning.
I can play the saxophone and knit an arran sweater with ease but I am new to database code.
If you are going to make comments explain what you mean or don't bother commenting!!!!

OK, if I tried to play the saxophone horizontally and I asked you what was the best way to use my fingers on the keys, the first thing you would probably tell me is it is meant to be played vertically.?
I am doing it that way as I am an excellent flute player and have played the flute for years, hence my using just another wind instrument in the only way I know how. (I can't play any instrument really, but this is analogy world)

Pretty much the same analogy with a database.
Each of the fields that are n, n+1, n+2 should be in their own record, with whatever other info is required.
Then you just use the Access Max() function to get what you want.

You are thinking in Excel which a lot of people do to start with.
I would be doing the same if I tried to play the saxophone as I played the flute.


How is that.

 

[Galaxiom]

Thanks for the advice. I am new to this and though I appreciate your comment it did zero to help

A perfect match then to the zero acknowledgement you gave to the advice that your structure is wrong.

The_Doc_Man noted that we both had repeatedly advised that the request indicated structural errors through the thread. All you needed to do was read those posts and you would have had your explanation.

But no. You completely ignored the advice of two professional developers with more than ten thousand posts each and only acknowledged the person with three posts who naively gave you what you naively wanted.