Results 1 to 3 of 3

Thread: Compare rows

  1. #1
    Join Date
    Mar 2005
    Posts
    55

    Exclamation Unanswered: Compare rows

    Is there a way to compare values in one row and return the smallest value eg.

    Carrier A B C D E
    Burson 1 2 0 4 5

    I want to be able to compare the values in Columns A,B,C,D,E and return the smalles value that is not '0'.

    In excel I use Large with the combination of count and countif to solve this, but i want to be able to do this in Access.

  2. #2
    Join Date
    Mar 2005
    Posts
    55
    I foudn this solution on another site

    Function Largest(ParamArray varValues()) As Variant
    Dim i As Integer 'Loop controller.
    Dim varMax As Variant 'Largest value found so far.

    varMax = Null 'Initialize to null

    For i = LBound(varValues) To UBound(varValues)
    If IsNumeric(varValues(i)) Then
    If varMax >= varValues(i) Then
    'do nothing
    Else
    varMax = varValues(i)
    End If
    End If
    Next

    Largest = varMax
    End Function

    Thanks to Allen Browne


    I guess it is poor DB design but the data set is from a supplier.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT carrier
         , Min(some_column_alias) As [top_one]
    FROM   (
            SELECT carrier
                 , a As [some_column_alias]
            UNION
            SELECT carrier
                 , b
            UNION
            SELECT carrier
                 , c
            UNION
            SELECT carrier
                 , d
            UNION
            SELECT carrier
                 , e
           ) As [some_table_alias]
    GROUP
        BY carrier
    George
    Home | Blog

Posting Permissions

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