var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: find max in specific rows
hey...i'm new to all of this and i'm in big 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 oculd be missing)
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.
with help, I've managed to do this for each day...but I dont know how go about modifying my code so that i can find the max for eahc month.
Here's my code so far:
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
'adding the highest value to a table together with its ID reference and date
UpdateRS!ID = MyRS!RECORDERID
UpdateRS!Date = MyRS!Date
UpdateRS!HighestValue = HighValue
Set MyRS = Nothing
Set UpdateRS = Nothing
if someone could give me some suggestions it would really be appreciated becuase it's important. thanks so much.
I Think with a Dmax function and array you can :
Dim result(4) As Double
' the field name in my test are Field1, field2, field3;etc....
' after you can retrieve the HiggestValue
For ColumnCounter = 1 To 4 'cycle through the columns
result(ColumnCounter) = DMax("Table4![Field" & ColumnCounter & "]", "Table4", "Month(Dateused) = 5") ' You can put a variable here to replace 5
For ColumnCounter = 1 To Ubound(Result)
If HighValue < Result(ColumnCounter) Then
HighValue = Result(ColumnCounter)
It looks like you are adding the daily highs to a separate table. If this is true then you can create a query to find the monthly high. Create two calculated fields and your HighestValue:
Then group on the Year and the Month fields and use the Max() function to give you the max for each month.