Still tying to find the Max

[Guest]

I am using Access 2003 and I am trying to find the max value across fields,
this is proving to be a very fruitless indevore. I really need some help
here!!

If you got anything...I have tried crosstab queries ... no luck

 

[Douglas J. Steele]

I suspect that the reason you're finding it difficult is because it isn't
really common to need to compare across fields in the same row.

If you've got multiple fields containing the same information in a single
row, odds are it's what's referred to as a repeating group, which shouldn't
exist.

If you've got Field1, Field2, Field3, odds are that should be 3 separate
rows in another related table, rather than 3 fields in a single row. Then,
it's easy to write a query that uses the Max function, or you can use the
DMax function in VBA. For the given example, you'd have to write something
like IIf([Field1] > [Field2], IIf([Field1] > [Field3], [Field1], [Field3]),
IIf([Field2] > [Field3], [Field2], [Field3]))

Unfortunately, since you've given no details, I can't offer anything more
explicit than that.

 

[John Vinson]

I am using Access 2003 and I am trying to find the max value across fields,
this is proving to be a very fruitless indevore. I really need some help
here!!

If you got anything...I have tried crosstab queries ... no luck

As Douglas says, you're not finding out how to do this because this
operation should not be necessary in a well-defined database. It
sounds like you're "committing spreadsheet", a venial sin punishable
by being required to read Codd & Date's textbook!

You can do it with just an expression like

=NZ([Field1]) + NZ([Field2]) + NZ([Field3]) <etc>, or... just for
fun... Here's some code that should make it possible:

Public Function SumAcross(ParamArray vValue() As Variant) As Double
Dim iPos As Integer
Dim dblSum As Double
dblSum = 0#
For iPos = 0 To UBound(vValue)
dblSum = dblSum + NZ(vValue(iPos))
Next iPos
SumAcross = dblSum
End Function

You could call this from a Query like

SumIt: SumAcross([Field1], [Field2], [Field3], ...)


John W. Vinson[MVP]

 

[Douglas J. Steele]

I am using Access 2003 and I am trying to find the max value across
fields,
this is proving to be a very fruitless indevore. I really need some help
here!!

If you got anything...I have tried crosstab queries ... no luck

As Douglas says, you're not finding out how to do this because this
operation should not be necessary in a well-defined database. It
sounds like you're "committing spreadsheet", a venial sin punishable
by being required to read Codd & Date's textbook!

You can do it with just an expression like

=NZ([Field1]) + NZ([Field2]) + NZ([Field3]) <etc>, or... just for
fun... Here's some code that should make it possible:

Public Function SumAcross(ParamArray vValue() As Variant) As Double
Dim iPos As Integer
Dim dblSum As Double
dblSum = 0#
For iPos = 0 To UBound(vValue)
dblSum = dblSum + NZ(vValue(iPos))
Next iPos
SumAcross = dblSum
End Function

You could call this from a Query like

SumIt: SumAcross([Field1], [Field2], [Field3], ...)

<picky>

That shows how to sum across the fields, John, not how to get the maximum,
which is what Rockwater was looking for.

Public Function MaxValue(ParamArray vValue() As Variant) As Double
Dim iPos As Integer
Dim dblMax As Double
dblMax = vValue(0)
For iPos = 1 To UBound(vValue)
If vValue(iPos) > dblMax Then
dblMax = vValue(iPos)
End If
Next iPos
MaxValue = dblMax
End Function

</picky>

 

[John Vinson]

That shows how to sum across the fields, John, not how to get the maximum,
which is what Rockwater was looking for.

<oops> <blush>

Thanks Douglas.

John W. Vinson[MVP]