calculations in Access 2003

[Guest]

I am trying to calculate a maximum value from 3 values. I understand how to
do that if they are in the same field (in a column), but have no luck if they
are from different fields. Example: We use a meter to measure a parameter
and do it three times. These 3 values are recorded in 3 fields (read.1,
read.2, read.3). I want to get a query or report or something to calculate
the max value from the three. Anyhelp would be great?

Thanks.

 

[Brendan Reynolds]

I am trying to calculate a maximum value from 3 values. I understand how
to
do that if they are in the same field (in a column), but have no luck if
they
are from different fields. Example: We use a meter to measure a
parameter
and do it three times. These 3 values are recorded in 3 fields (read.1,
read.2, read.3). I want to get a query or report or something to
calculate
the max value from the three. Anyhelp would be great?

Thanks.

Here's something I posted recently in response to a similar question
involving the latest of three date fields. Substitue your three meter
readings for the three dates.

<quote>
I agree fully with Duane's advice elsewhere in this thread, that the best
solution is, if possible, to change the database design. Because there are
times when we are forced to deal with non-normalized data from 'legacy' data
sources, here's an example of how you could achieve the desired result with
the existing database design. But I would not recommend doing it except as a
last resort - the queries are tedious to write, and inefficient in use, as
they can not make use of any indexes that might exist on these fields.

The solution requires VBA code such as this in a standard module ...

Public Function FMax(ParamArray Values() As Variant) As Variant

Dim varResult As Variant
Dim varLoop As Variant

varResult = Null
For Each varLoop In Values
If IsNull(varResult) Or (varLoop > varResult) Then
varResult = varLoop
End If
Next varLoop

FMax = varResult

End Function

You can now call this function in a query like this ..

SELECT tblTest.TestDate1, tblTest.TestDate2, tblTest.TestDate3,
FMax([TestDate1],[TestDate2],[TestDate3]) AS TestFMax
FROM tblTest;

</quote>