Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    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:
    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.

  2. #2
    Join Date
    Sep 2004
    Posts
    161
    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....
    Code:
    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
    Next ColumnCounter
    ' after you can retrieve the HiggestValue
    Code:
    For ColumnCounter = 1 To Ubound(Result)
    If HighValue < Result(ColumnCounter) Then
    HighValue = Result(ColumnCounter)
    End If
    End Sub

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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:

    Code:
    Year:DatePart(Date,"yyyy")
    Month:DatePart(Date,"m")
    HighestValue

    Then group on the Year and the Month fields and use the Max() function to give you the max for each month.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •