Max of multiple columns (1 Viewer)

[ppataki]

Dear All,

I have a table with 4 fields and I would like to insert a fifth field that would calculate the max for each record (so the max value of the 4 fields)

Eg.
Field1---Field2---Field3---Field4----Newfield
2---------3-------4--------1---------4
5---------6--------5--------2--------6

Could you please help me how to achieve that?

Many thanks!!

 

[dbDamo]

No you don't want to insert a fifth field into your table, you want to create a query with a calculated field that will calculate the max value.

 

[ppataki]

Yes, that is exactly what I would like to do, but I dont know how
Anyone could please help?

Many thanks

 

[dbDamo]

Ok, now that you know what you are doing and hopefully why you are doing it, I will tell you how -

Firstly, paste this code into a module -

Function Minimum(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 smallest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I
 
' Return the minimum value found.
Minimum = currentVal
 
End Function
 
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

Then create a query based on your table in design view. Drag your 4 fields into the query grid. In the fifth column of the query grid, type

Maximum value: Maximum([field1],[field2],[field3],[field4])

Now save and run your query!

 

[ppataki]

Woow! It works perfectly!
Thank you very much for your help, I really appreciate it!

 

[dbDamo]

Absolutely not a problem, just make sure you remember to never store a calculated field in a table!

 

[svaness]

Thank you so much dbDamo. It seems to work fine for me too, except that some of my columns do not have data, so when it finds null values in the first column, it doesn't find the maximum for those records. Can you please help me further?

Thanks

 

[Poppa Smurf]

Put NZ in front of the fields in Maximum value: Maximum([field1],[field2],[field3],[field4]) e.g.Maximum value: Maximum(nz([field1]),nz([field2]),nz([field3]),nz([field4])) this will give a value of zero.
For the Minimum use the format nz([field1],x) where x would be some high value that would not be considered as a low value. e.g. 99999. Do the same for the other fields.

 

[vbaInet]

Based on Poppa Smurf's advice, it might be better to put the Nz() in the function so you don't have to type Nz() all the time.

 

[svaness]

Thank you Poppa smurf! That worked great!

vbaInet: where in the function would I actually put nz? I'm still learning VBA.

Thank you for all your help!

 

[vbaInet]

Whereever you see FieldArray() put Nz() around it, like this:

Nz(FieldArray())

 

[svaness]

vbaInet: It worked perfect. Thank you!

 

[vbaInet]

Glad to hear.

 

[The_Doc_Man]

It is your app, so no criticism intented, but I hope you DO realize that if you can do a meaningful Max function applied cross-column, the columns are close enough in meaning that your design is probably not correctly normalized. Which would imply that a design change would trivialize the problem no matter HOW many columns you have.

 

[New2Access_and_VBA]

Hi,
your given code is not working for my database. I have 11 fields(field6,field7,field8........,field16) in it, at some places it is working but not at all places. I have pasted the code in the module and saved it then went to design view of query and enter all 11 fields and wrote the formula in next column in design view of query and named it max: but the result is not correct, i have attached the screen shot of my access result .jpeg is attached:

Kindly help me

 

[New2Access_and_VBA]

the formula i wrote in the design view of query was Maximum([field6],[field7],[field8],[field9],[field10],[field11],[field12],[field13],[field14],[field15])

 

[Poppa Smurf]

Looking at your screen shot it appears your data is a string and not numeric.
Use the formula that you posted and assuming the data is a string the the result is correct. For a string all data starting with the digit 1 are grouped together followed by digit 2 etc. The two rows that are correct are correct in that there are no 3 digit value. In the first row of data it assumes the string literal 7 is greater than the string literal 38.

You coudl ensure the data is numeric or place VAL before the field e.g. Maximum(VAL([field6]),VAL([field7])......VAL([field16]))

 

[New2Access_and_VBA]

Hi Poppa,
You are awesome man! your suggestion worked perfectly, I am very grateful to you. You and dbDemo are the real Kings of Access. Gratitude

 

[Poppa Smurf]

I am glad this solved your problem. All the best

 

[copleyr]

Ok, now that you know what you are doing and hopefully why you are doing it, I will tell you how -

Firstly, paste this code into a module -

Function Minimum(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 smallest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I
 
' Return the minimum value found.
Minimum = currentVal
 
End Function
 
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

Then create a query based on your table in design view. Drag your 4 fields into the query grid. In the fifth column of the query grid, type

Maximum value: Maximum([field1],[field2],[field3],[field4])

Now save and run your query!

I did this and received a "Undefined function 'Maximum' in expression" error. Any suggestions? Attached is a screenshot...