Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    15

    Unanswered: find Max and Min values in a column

    I need to find the max and min values in a column. I have written this code but it seems that there should be a more efficient way to do this. How would you pros handle it?

    Thanks


    Dim sngMaxScore As Single, sngMinScore As Single
    'Query the max score
    strSQL = "SELECT Max(FlexibilityTable.LowLimit) AS MaxScore " & _
    "FROM FlexibilityTable " & _
    "GROUP BY FlexibilityTable.Joint, FlexibilityTable.Motion " & _
    "HAVING FlexibilityTable.Joint = '" & [strJoint] & "'" & _
    " AND FlexibilityTable.Motion = '" & [strMotion] & "';"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    sngMaxScore = rs("MaxScore")
    Set rs = Nothing

    'Query the min score
    strSQL = "SELECT Min(FlexibilityTable.LowLimit) AS MinScore " & _
    "FROM FlexibilityTable " & _
    "GROUP BY FlexibilityTable.Joint, FlexibilityTable.Motion " & _
    "HAVING FlexibilityTable.Joint = '" & [strJoint] & "'" & _
    " AND FlexibilityTable.Motion = '" & [strMotion] & "';"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    sngMinScore = rs("MinScore")
    Set rs = Nothing

    Select Case sngRangeOfMotion
    Case Is <= sngMinScore
    varITAScoreROM = 1
    Case Is >= sngMaxScore
    varITAScoreROM = 4
    End Select

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by new2access123 View Post
    How would you pros handle it?
    Code:
    SELECT MAX(LowLimit) AS MaxScore
         , MIN(LowLimit) AS MinScore
      FROM FlexibilityTable 
     WHERE Joint = '" & [strJoint] & "'
       AND Motion = '" & [strMotion] & "'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Posts
    15
    I knew there had to be a better way. I'm always learning here.

    Thank you

Posting Permissions

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