• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

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.
0
ltdanp22
Asked:
ltdanp22
2 Solutions
 
AkenathonCommented:
select ID, greatest(Week1, Week2, Week3, ... , Week52) from the_table
0
 
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
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Only works with numbers but also can be changed a little to work with strings, dates, etc...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now