Another calculation problem

[stryder09]

Okay I have 5 fields that I am looking at each field was assigned a value of either 0,2,5 or 20 depending on the criteria it meets. I now have a summary that looks at these values and I need it to only take the highest one. How do I do that?



ex. [field1]=0, [field2]=5, [field3]=2, [field4]=20 and [field5]= 2. I need the summary to take the highest and apply it to [fieldsummary]. In this example [field4]=20 so [fieldsummary] needs to also =20.

I hope I have given everything needed.

Thanks

Brad

[alansidman]

Here is a solution. Not the prettiest, but if I understand your situation, it will work.

SELECT Table1.ID, Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4, Table1.Field5,
 IIf([field1]>[field2] And [field1]>[field3] And [field1]>[field4] And [field1]>5,[field1],
IIf([field2]>[field1] And [field2]>[field3] And [field2]>[field4] And [field2]>[field5],[field2],
IIf([field3]>[field1] And [field3]>[field2] And [field3]>[field4] And [field3]>[field5],[field3],
IIf([field4]>[field1] And [field4]>[field2] And [field4]>[field3] And [field4]>[field5],[field4],[field5])))) AS Fieldsummary
FROM Table1;

alan

[stryder09]

So I will have to write this in code then?

[alansidman]

Or cut and paste it. Look at the attachment also.
If you need to change the field names, cut and paste into a word document and do a find and replace. Then cut and paste into your query.

Alan

[stryder09]

Thank you it worked perfectly for me.

Brad

[pbaldy]

Perhaps simpler and more flexible:

Minimum or Maximum Value Across Fields of Record

[stryder09]

Ok I kind of have this working with the equation below. My only problem now is if any of the 5 fields has the same number it will not give me anything. Any ideas?

=IIf([pH1Demerits]>[pH2Demerits] And [pH1Demerits]>[pH3Demerits] And [pH1Demerits]>[pH4Demerits] And [pH1Demerits]>[pH5Demerits],[pH1Demerits],IIf([pH2Demerits]>[pH1Demerits] And [pH2Demerits]>[pH3Demerits] And [pH2Demerits]>[pH4Demerits] And [pH2Demerits]>[pH5Demerits],[pH2Demerits],IIf([pH3Demerits]>[pH1Demerits] And [pH3Demerits]>[pH2Demerits] And [pH3Demerits]>[pH4Demerits] And [pH3Demerits]>[pH5Demerits],[pH3Demerits],IIf([pH4Demerits]>[pH1Demerits] And [pH4Demerits]>[pH2Demerits] And [pH4Demerits]>[pH3Demerits] And [pH4Demerits]>[pH5Demerits],[pH4Demerits],IIf([pH5Demerits]>[pH1Demerits] And [pH5Demerits]>[pH2Demerits] And [pH5Demerits]>[pH3Demerits] And [pH5Demerits]>[pH4Demerits],[pH5Demerits])))))

Thanks

Brad

[ssanfu]

When I tried the formula, I kept getting a compile error (in VBA). The last IIF() was missing the "FalsePart" if the function.

This is what finally worked:

= IIf([pH1Demerits] > [pH2Demerits] And [pH1Demerits] > [pH3Demerits] And [pH1Demerits] > [pH4Demerits] And [pH1Demerits] > [pH5Demerits], [pH1Demerits], IIf([pH2Demerits] > [pH1Demerits] And [pH2Demerits] > [pH3Demerits] And [pH2Demerits] > [pH4Demerits] And [pH2Demerits] > [pH5Demerits], [pH2Demerits], IIf([pH3Demerits] > [pH1Demerits] And [pH3Demerits] > [pH2Demerits] And [pH3Demerits] > [pH4Demerits] And [pH3Demerits] > [pH5Demerits], [pH3Demerits], IIf([pH4Demerits] > [pH1Demerits] And [pH4Demerits] > [pH2Demerits] And [pH4Demerits] > [pH3Demerits] And [pH4Demerits] > [pH5Demerits], [pH4Demerits], IIf([pH5Demerits] > [pH1Demerits] And [pH5Demerits] > [pH2Demerits] And [pH5Demerits] > [pH3Demerits] And [pH5Demerits] > [pH4Demerits], [pH5Demerits], 1111)))))

I also wrote a UDF: (order of the arguments doesn't matter)

Public Function MaxDemerit(p1 As Integer, p2 As Integer, p3 As Integer, p4 As Integer, p5 As Integer) As Integer
   Dim MaxValue As Integer

   MaxValue = 0

   If p1 > MaxValue Then
      MaxValue = p1
   End If
   If p2 > MaxValue Then
      MaxValue = p2
   End If
   If p3 > MaxValue Then
      MaxValue = p3
   End If
   If p4 > MaxValue Then
      MaxValue = p4
   End If
   If p5 > MaxValue Then
      MaxValue = p5
   End If

   MaxDemerit = MaxValue

End Function

To call it, use:

for a textbox on a form:

= MaxDemerit(pH1Demerits, pH2Demerits, pH3Demerits, pH4Demerits, pH5Demerits)

in a query:

Fieldsummary: MaxDemerit(pH1Demerits, pH2Demerits, pH3Demerits, pH4Demerits, pH5Demerits)

[stryder09]

So when I use the formula you gave me Steve it returns the value 1111 if I have two or more values the same? It still will not just give me the highest value. Can I not just create a formula for the form page or does this have to be done in Visual Basic? I am not at all fluent in Visual BAsic.