Query question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
..
 
That worked - thanks so much!

John Spencer said:
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
..

Golfinray said:
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
Click to expand...
Click to expand...
 
That worked - thanks so much!
Michel Walsh said:
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
Click to expand...
 
Back
Top