Query fields for maximum value

[swift]

Hi again,

This time I’m looking for a way for my query to tell me which is the maximum value out of three fields in the query, in this instance its [LPV3mScore], [LPV10mScore] and [LPV30mScore]. For example. If
[LPV3mScore] = 0
[LPV10mScore] = 19
[LPV30mScore] = 0

Then I would like to return LPVMaxScore = 19

[LPV3mScore] = 79
[LPV10mScore] = 56
[LPV30mScore] = 43

Then I would like to return LPVMaxScore = 76

This is my SQL at present, I’ve tried using the maximum function in expression builder but its the bit highlighted in red thats giving me problems! Can anyone help?

SELECT Round([MaximumRut],0) AS MaxRut, RutScore([MaxRut]) AS RutScore, Round([TEX],1) AS Texture, TextureScore([Texture]) AS TextureScore, Round([WholeCwayCracking],2) AS Cracking, CrackingScore([Cracking]) AS CrackingScore, Round([LPV3m],1) AS 3mELPV, LPV3mScore([3mELPV]) AS LPV3mScore, Round([LPV10m],1) AS 10mELPV, LPV10mScore([10mELPV]) AS LPV10mScore, Round([LPV30m],0) AS 30mELPV, LPV30mScore([30mELPV]) AS LPV30mScore, [COLOR="Red"]Max([LPV3mScore] And [LPV10mScore] And [LPV30mScore]) AS LPVMaxScore[/COLOR]
FROM [TRACS Details];

Thanks in advance

Swifty

 

[Galaxiom]

Ideally you should restructure the data so the Scores are in the same field. Another field designates the score type. This is much easier to work with.

 

[swift]

Ideally you should restructure the data so the Scores are in the same field. Another field designates the score type. This is much easier to work with.

Sorry, but you've completely lost me there! could you elaborate?

cheers

 

[Galaxiom]

Databases work best where information for comparision is in the same field. Other attributes that define attributes of the data are held as values in other fields of the record.

RecordNum | LPVType | Score
    1     |     3   |    79
    1     |    10   |    56
    1     |    30   |    43
    2     |     3   |     0
    2     |    10   |    19
    2     |    30   |     0

In this structure the information required is done in a query with Group By RecordNum then return Max of the Score.

 

[swift]

Databases work best where information for comparision is in the same field. Other attributes that define attributes of the data are held as values in other fields of the record.

RecordNum | LPVType | Score
    1     |     3   |    79
    1     |    10   |    56
    1     |    30   |    43
    2     |     3   |     0
    2     |    10   |    19
    2     |    30   |     0

In this structure the information required is done in a query with Group By RecordNum then return Max of the Score.

The one issue I have here is that the data is loaded up from excel in a standard format, so this (I think) is not possible unless I can devise a way around?

cheers

 

[DCrake]

Take a look at the attachment in post #21 in this thread. It should give you what you want.

 

[swift]

Brilliant, Dcrake, thanks!

One thing though, I was looking at the last field on the query in that DB. I could use something similar in my query to tell me which category (LPV3m, LPV10m or LPV30m) has the maximum (highest) value.
In the downloaded DB, it returns the question(s) answered with the worst percentage. What I’d like to do is return either Category 1, Category 2 or Category 3 in that query (where Category 1 = LPV3m, Category 2 = LPV10m and Category 3 = LPV30m).
However, I have another set of criteria to add (which is where I have the problem!) Occasionally, the values for LPV3m and LPV10m may be identical (also LPV10m and LPV30m; LPV30m and LPV10m; or even all three the same). In this instance I would like to return the highest category. For example, if LPV3m and LPV30m are identical, then return Category 1. Or if LPV10m and LPV30m are identical, then return Category 2)

This is what I have so far in the function (which I've just mangled from yours!):

Public Function MaxLPVType(ParamArray FieldArray() As Variant) As String
   ' Declare the two local variables.
   Dim I As Integer
   Dim strItems As String
   Dim Lowest As Double
   Dim qCnt As Integer

   ' Set the variable currentVal equal to the array of values.
   currentVal = FieldArray(0)

   ' Cycle through each value from the row to find the highest.
   For I = 0 To UBound(FieldArray)
   
      If FieldArray(I) >= currentVal Then
         currentVal = FieldArray(I)
         Highest = currentVal
      End If
   Next I
    
   'next rerun the loop to pick out all the questions that match the highest answer
   For I = 0 To UBound(FieldArray)
   
      If FieldArray(I) = Highest Then
         strItems = strItems & I + 1
         qCnt = qCnt + 1
      End If
   Next I
     
   Select Case qCnt
    Case 1
        strItems = "Category " & strItems
    End Select
    'finally strip out any extra spaces that may have crept in
     strItems = Replace(strItems, "  ", " ")
   ' Return the results
   MaxLPVType = strItems

Sorry if that doesn't make any sense, I would really like to make this work, seems like it could save a lot of time for me!

Thanks again!