Find Max in specific rows

[yoonie_85]

hello...i'm totally new to all of this and i'm in need of some help

i have a table in access that has:
1st column - customer names
2nd column - the date from may1-august31 (some days are missing for certain cutomers)
column3 - column26 - data for the 24hrs in 1 day.

I was requested to find the max of each row between columns 9-21 for each month.

I've managed to do this for each day and output to a new table calle UpdateTable...but I dont know how go about modifying my code so that i can find the max for eahc month. the dates are in the format mm/dd/yyyy.

Here's my code so far:
Sub Highest()
Dim MyRS As Recordset
Dim UpdateRS As Recordset
Dim ColumnCounter As Long
Dim HighValue As Double 'or whatever


Set MyRS = CurrentDb.OpenRecordset("Data", dbOpenForwardOnly)
Set UpdateRS = CurrentDb.OpenRecordset("UpdateTable", dbOpenDynaset)

Do Until MyRS.EOF
HighValue = 0
For ColumnCounter = 9 To 21 'cycle through the columns
If HighValue < MyRS.Fields(ColumnCounter).Value Then
HighValue = MyRS.Fields(ColumnCounter).Value
End If

Next ColumnCounter

'adding the highest value to a table together with its ID reference and date
UpdateRS.AddNew
UpdateRS!ID = MyRS!RECORDERID
UpdateRS!Date = MyRS!Date
UpdateRS!HighestValue = HighValue
UpdateRS.Update

MyRS.MoveNext
Loop

MyRS.Close
Set MyRS = Nothing
UpdateRS.Close
Set UpdateRS = Nothing
End Sub

if someone could give me some suggestions it would really be appreciated becuase it's important. thanks so much.

 

[Rich]

I would suggest that you Normalise your data, as it is you've re-created a spreadsheet in Access. Search here for articles on normalis(z)ation