Query question

[Guest]

I have tried and tried to figure this out and have not. If I have 10 fields
in a query (all numbers, not text) and I want an 11th field to return the
highest row value in that row (inother words, I have
159,134,190,167,122,189,201,182,174,140 as the values in one row) and I want
the 11th row to return 201 (the highest value in that row) How? Any help
greatly appreciated. Thanks

 

[Michel Walsh]

Well, databases work VERTICALLY. You should have 10 rows, rather than one:

someId, f1, f2, f3, ..., f10 ' fields name
1 10 21 31 ... 108 ' data



the following would be preferable, for a database:


someId, kind, theValue 'fields name
1 f1 10
1 f2 21
1 f3 31
....
1 f10 108




It will then be a simpler matter to use MAX(theValue), since now, the data
is vertical. (You can switch from a vertical presentation to an
'horizontal' presentation, when required, for human reading of the data, as
example, with a CROSSTAB query.)


If you are doomed to use the horizontal (not-normalized) presentation, then
you can write a VBA function, in a standard module (not under a class, a
form, a report), that cycles through its arguments, returning the maximum
found:

===========================

Public Function vbMax(ParamArray x() As Variant) As Variant

Dim localMax As Variant
Dim localCurrent As Variant
For Each localCurrent In x
If IsMissing(localCurrent) Then
ElseIf IsNull(localCurrent) Then
ElseIf IsNull(localMax) Then
localMax = localCurrent
ElseIf localCurrent > localMax Then
localMax = localCurrent
End If
Next localCurrent

vbMax = localMax


End Function

===========================


and use:

MyMax: vbMax(f1, f2, f3, f4, f5, f6, f7, f8, f9, f10 )


in your query (assuming the field names are f1 to f10).




Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

You will need a custom vba function saved in a module and then call that in
your query.

'Dale Fye posted this quite a while ago. Paste it into a module and save
the module (with a different name)

Public Function MaxVal(ParamArray MyArray()) As Variant

Dim varMax As Variant
Dim intLoop As Integer
varMax = Null

For intLoop = LBound(MyArray) To UBound(MyArray)
If IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf IsNull(varMax) Or MyArray(intLoop) > varMax Then
varMax = MyArray(intLoop)
End If
Next
MaxVal = varMax

End Function

In design view of a query

Field: TheBigOne: MaxVal([Field1],[Field2],[Field3],[Field4],[Field5],
....,[Field9])

In an SQL statement
SELECT MaxVal([Field1],[Field2],[Field3],[Field4],[Field5], ...,[Field9]) as
TheBigOne
FROM [SomeTable]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

That worked - thanks so much!

You will need a custom vba function saved in a module and then call that in
your query.

'Dale Fye posted this quite a while ago. Paste it into a module and save
the module (with a different name)

Public Function MaxVal(ParamArray MyArray()) As Variant

Dim varMax As Variant
Dim intLoop As Integer
varMax = Null

For intLoop = LBound(MyArray) To UBound(MyArray)
If IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf IsNull(varMax) Or MyArray(intLoop) > varMax Then
varMax = MyArray(intLoop)
End If
Next
MaxVal = varMax

End Function

In design view of a query

Field: TheBigOne: MaxVal([Field1],[Field2],[Field3],[Field4],[Field5],
....,[Field9])

In an SQL statement
SELECT MaxVal([Field1],[Field2],[Field3],[Field4],[Field5], ...,[Field9]) as
TheBigOne
FROM [SomeTable]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have tried and tried to figure this out and have not. If I have 10
fields
in a query (all numbers, not text) and I want an 11th field to return the
highest row value in that row (inother words, I have
159,134,190,167,122,189,201,182,174,140 as the values in one row) and I
want
the 11th row to return 201 (the highest value in that row) How? Any help
greatly appreciated. Thanks

 

[Guest]

That worked - thanks so much!

Well, databases work VERTICALLY. You should have 10 rows, rather than one:

someId, f1, f2, f3, ..., f10 ' fields name
1 10 21 31 ... 108 ' data



the following would be preferable, for a database:


someId, kind, theValue 'fields name
1 f1 10
1 f2 21
1 f3 31
....
1 f10 108




It will then be a simpler matter to use MAX(theValue), since now, the data
is vertical. (You can switch from a vertical presentation to an
'horizontal' presentation, when required, for human reading of the data, as
example, with a CROSSTAB query.)


If you are doomed to use the horizontal (not-normalized) presentation, then
you can write a VBA function, in a standard module (not under a class, a
form, a report), that cycles through its arguments, returning the maximum
found:

===========================

Public Function vbMax(ParamArray x() As Variant) As Variant

Dim localMax As Variant
Dim localCurrent As Variant
For Each localCurrent In x
If IsMissing(localCurrent) Then
ElseIf IsNull(localCurrent) Then
ElseIf IsNull(localMax) Then
localMax = localCurrent
ElseIf localCurrent > localMax Then
localMax = localCurrent
End If
Next localCurrent

vbMax = localMax


End Function

===========================


and use:

MyMax: vbMax(f1, f2, f3, f4, f5, f6, f7, f8, f9, f10 )


in your query (assuming the field names are f1 to f10).




Hoping it may help,
Vanderghast, Access MVP