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!!!!

 

[Uncle Gizmo]

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

I disagree with your observation that you got "zero help". What you actually got was advice that you don't like, hence your dissatisfaction with it.

You're not the first and you will not be the last person that comes here with a database constructed incorrectly, and then ignore the advice to look at the structure and fix it.

That's good advice you are ignoring!

I spell it out on my website here:-


https://www.niftyaccess.com/excel-in-access/



Sent from Newbury UK

 

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

 

[Uncle Gizmo]

If you are going to make comments explain what you mean or don't bother commenting!!!!

Oh! A bitch! I like taking down bitches.

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

Sent from Newbury UK

 

[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