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

1. Registered 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

2. Cavalier 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

3. Cavalier 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

4. Cavalier 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

Calc = biggestOf6(A, B, C, D, E, F)

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

izy

5. Registered 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

6. Cavalier 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.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
•