Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    DFW
    Posts
    2

    Question Unanswered: How to calculate minimum (or maximum) among 6 columns for every row?

    Hi all,

    I am fairly new to MS ACCESS and SQL. I am trying to create a query that will list the minimum (or maximum) value among multiple columns(fields) within the same record in a newly created field for every row. For example, pls see below:

    Field 1, Field 2, Field 3, Field 4, Field 5, Field 6, New (calculated)Field
    10, 23, 35, 14, 25, 1, 1
    2, 5, 3, 8, 9, 0, 0

    and so on. There are over several thousand records (hence copying to Excel and using min function from Excel is not an option) and I need to figure the value of New Field as indicated above.

    Can you pls help me with the sample query for the above? If we need to use Nested IIF statements with the expression builder, can someone pls provide a sample for the above? I appreciate all your help in advance. Thanks much.

    Regards,

    RG

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    there is probably a clever way, but here is a stupid way: ABCDE are your fieldNames

    Calc = iif([A]>[B],[A],[B])
    'finds biggest of AB

    Calc = iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B]))
    'finds biggest of ABC

    Calc = iif([D]>iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B])),[D],iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B])))
    'finds biggest of ABCD

    and finally:
    Calc = iif([E]>iif([D]>iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B])),[D],iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B]))),[E],iif([D]>iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B])),[D],iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B]))))
    'find biggest of ABCDE

    izy

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry, you said SIX fields, so one more iteration:

    Calc = iif([F]>iif([E]>iif([D]>iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B])),[D],iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B]))),[E],iif([D]>iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B])),[D],iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B])))),[F],iif([E]>iif([D]>iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B])),[D],iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B]))),[E],iif([D]>iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B])),[D],iif([C]>iif([A]>[B],[A],[B]),[C],iif([A]>[B],[A],[B])))))

    ...and no, i've no idea if something this messy will evaluate!

    izy

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thinking about it, that's quite an absurd expression. maybe better is to make yourself a function in a global module:

    public function biggestOf6(A as long, B as long, C as long, D as long, E as long, F as long) as long
    'if they are not longintegers, change all the AS to whatever they are.
    dim biggest as long
    if A>B then
    biggest = A
    else
    biggest = B
    end if
    if C>biggest then biggest = C
    if D>biggest then biggest = D
    if E>biggest then biggest = E
    if F>biggest then biggest = F
    biggestOf6 = biggest
    end function

    and youR query than uses:
    Calc = biggestOf6(A, B, C, D, E, F)

    ...A, B etc being replaced by your real filed names.

    izy

  5. #5
    Join Date
    Jul 2003
    Location
    DFW
    Posts
    2

    Smile

    Hi Izy,

    Thanks a lot for your detailed response. I will try out your suggestions right away and hopefully they should work. I appreciate your help. Thanks again.

    Best regards,

    RG

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    there is also a neat little function in msowcf.dll
    Min(A, B, C, D, E, F, G ....etc)

    but making msowcf fly seems to be ...problematic! see:

    http://dbforums.com/arch/219/2002/8/484825
    http://dbforums.com/t565769.html
    http://www.experts-exchange.com/Data..._20418014.html


    izy

Posting Permissions

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