Access 97 Query Question 1

[wfweirich]

Can you create a column in a query that will return the greatest value of a group of other columns. For example, you have a record in a table for each machine number and four readings, a total of five fields. For machine #1 you want to know the highest reading. So, the sixth column in your query should contain the greatest value of the previous 4 column values. I am looking for the query expression that will do this. Thanks in advance for your help.

 

[WHM]

I am certain that there is an easier method, but I am not aware of it. This uses the notion of combinational logic, and I think it will work:

In your WHERE clause try something like this

....WHERE (((T.F1>T.F2) AND (T.F1>T.F3) AND (T.F1>T.F4) AND (T.F1>T.F5)) OR ((T.F2>T.F1) AND (T.F2>T.F3) AND (T.F2>T.F4) AND (T.F2>T.F5)) OR ............

You should get the idea, there will always be a single case where one of the ORs is true.

Hope you follow.

Hunter

 

[PaulF]

try creating a query with the fields you want and make the last one an expression field that uses a User Defined Function that you will write and place in a Module, and you pass the values of each field simply by using the Field Names in the Expression

An Example would be
In the query add a field such as this
Highest Reading: GetHighest([Field1],[Field2],[Field3],[Field4])

just replace [Field1] thru [Field4] with the actual Field Names in your table

In a Module add this

Public Function GetHighest(var1 as variant, var2 as variant, var3 as variant, var4 as variant)

Dim varHighest as Variant
varHighest = var1
If var2 > varHighest Then varHighest = var2
If var3 > varHighest Then varHighest = var3
If var4 > varHighest Then varHighest = var4
GetHighest = varHighest
End Function

you can use a variable type other than Variant to save Memory, I just used that because I don't have a clue as to the type of data in your fields.

One other thing to be careful of is Nulls.....Access and its Nulls......you might need to pass the Fields using something like this
NZ([Field1],0)
instead of just [Field1]

PaulF

 

[nomus1]

I am sure there are other ways, but if you added a function to a module like this:

Public Function getmax(lookup1 As Long, lookup2 As Long, Optional lookup3 As Long, Optional lookup4 As Long) As Long
On Error GoTo getmax_Err
Dim holdval As Long

If lookup1 > lookup2 Then
holdval = lookup1
Else
holdval = lookup2
End If

If Nz(lookup3, 0) > 0 Then
If holdval > lookup3 Then
holdval = holdval
Else
holdval = lookup3
End If

If Nz(lookup4, 0) > 0 Then
If holdval > lookup4 Then
holdval = holdval
Else
holdval = lookup4
End If
Else
Exit Function
End If
Else
Exit Function
End If

getmax_Exit:
getmax = holdval
Exit Function
getmax_Err:
msgbox ERR.NUMBER & " - " & ERR.Description, vbCritical
Resume getmax_Exit
End Function

Then your 6th column could be a formula like
holdno = getmax(nz(field1,0),nz(field2,0),nz(field3,0),nz(field4,0))


Hope this works for you