Results 1 to 6 of 6

072503, 14:16 #1Registered User
 Join Date
 Jul 2003
 Location
 DFW
 Posts
 2
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

072603, 05:14 #2Cavalier King Charles
 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

072603, 05:21 #3Cavalier King Charles
 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

072603, 08:02 #4Cavalier King Charles
 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

072603, 13:29 #5Registered User
 Join Date
 Jul 2003
 Location
 DFW
 Posts
 2
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

072703, 13:52 #6Cavalier King Charles
 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.expertsexchange.com/Data..._20418014.html
izy