How do I find the max value among fields in each record of a recordset?

I have a table with the following fields:

ID, Week1, Week2, Week3, ... , Week52

For each record, I need to identify the week with the maximum value. What's the best way to do this using VBA, queries, etc.
ltdanp22Asked:
Who is Participating?
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, you can also try this:

In the Access DB, add this function on a module:
Public Function MaxColumnValue(ParamArray values() As Variant) As Long
    Dim max As Long
    Dim i As Integer
    For i = 0 To UBound(values)
        If values(i) > max Then
            max = values(i)
        End If
    Next i
    MaxColumnValue = max
End Function

Open in new window


Then you can use it on a query, like this:
SELECT
        MaxColumnValue(A, B, C, D)
FROM
        Test;

Open in new window


Example DB attached (Access 2002-2003 format):
 MaxValues.mdb
0
 
AkenathonCommented:
select ID, greatest(Week1, Week2, Week3, ... , Week52) from the_table
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Only works with numbers but also can be changed a little to work with strings, dates, etc...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
broro183Commented:
hi,

I can't remember where I downloaded this from but it works for me in Access databases to identify the max value. It can be used in queries as "maximum(...)".

Option Compare Database

Function Minimum(ParamArray FieldArray() As Variant)
   ' Declare the two local variables.
   Dim i As Long
   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 = LBound(FieldArray) 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 Long
   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 = LBound(FieldArray) To UBound(FieldArray)
      If FieldArray(i) > currentVal Then
         currentVal = FieldArray(i)
      End If
   Next i

   ' Return the maximum value found.
   Maximum = currentVal

End Function

Open in new window



 To get the corresponding name of the highest value field you could change it to...
Sub UpdateTableWithMaxWeekNames()
Dim rs As DAO.Recordset
Dim MaxVal As Double
Dim MaxWkName As String
Dim i As Long

Set rs = CurrentDb.OpenRecordset("WeeklyValues", dbOpenDynaset)
With rs
    If Not (.BOF And .EOF) Then
    Do While Not .EOF
        
        For i = 1 To 52
            If .Fields("Week" & i) > MaxVal Then
                MaxVal = .Fields("Week" & i).Value
                MaxWkName = "Week" & i
            End If
        Next i
        .Edit
        .Fields("HighestWeek").Value = MaxWkName
        .Update
        .MoveNext
    Loop
    End If
    .Close
End With

Set rs = Nothing
MsgBox "Done"
End Sub

Open in new window


hth
Rob

__________________
Rob Brockett. Always learning & the best way to learn is to experience...
0
 
broro183Commented:
Drat, I must remember to refresh the page before posting...

Ah well...
I think that my UpdateTableWithMaxWeekNames does what you want. You can change the name of the Table & the output field ("highestWeek") as required.

Akenathon,
I can't get "greatest" to work in Access, should it?
Do I need to set a reference, or is it relying on something you haven't posted?

Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...
0
 
broro183Commented:
hi ltdanp22,

Have any of our posts helped/are we on the right track?
I would appreciate some feedback and I'm guessing the others may feel the same.

Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...
0
 
ltdanp22Author Commented:
Sorry guys. Used the procedures above and forgot to accept a solution. Akenathon, Greatest doesn't seem to work in Access.
0
 
broro183Commented:
Thanks for getting back to us - I'm pleased we could help :-)

Rob
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Me too! thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.