Finding max value

[DKS]

This seemed a simple one for me, but got stuck somewhere. Can somebody help?

I have a table with readings of several instruments on different dates.
There are several hundreds instruments. For some instruments, reading may
not be taken every day. There are no readings taken on weekends and holidays.

I need a query that will accept as input the name of the instrument and a
date range (start date and end date). For this combination of input
parameters I want to determine what was the max and min reading for the
instrument - and the date of this max and min reading.

If putting both the max and min in the same query can complicate affairs
then I do not mind having one query for max value, and another query for min
value.

Can someone help?

Many thanks in anticipation.

 

[vanderghast]

Assuming the data is in a table like:

pk datestamp instrumentID reading ' fields


where pk is the primary key (not use here after), datestamp the date and
time of the reading (the value) made by the instrumentID specified in the
same record, then, have:



SELECT instrumentID, MIN(reading) AS low, MAX(reading) AS high
FROM yourTableName
WHERE dateStamp BETWEEN startingDate AND endingDate
AND instrumentID = whichInstrument


as a saved query, Q1. Note the parameters: startingDate, endingDate and
whichInstrument.

Then have:

SELECT a.instrumentID, a.datestamp, a.reading
FROM yourTableName AS a INNER JOIN q1 AS b
ON a.instrumentID = b.instrumentID
AND (a.reading= b.low OR a.reading=b.high)
ORDER BY a.instrumentID, a.reading

should return what you were looking for.

Note that if the minimum/maximum occurs more than once, each occurence (of
the min, or max) will be returned. If you just want one, use, instead:


SELECT a.instrumentID, LAST(a.datestamp), a.reading
FROM yourTableName AS a INNER JOIN q1 AS b
ON a.instrumentID = b.instrumentID
AND (a.dateStamp = b.low OR a.dateStamp=b.high)
GROUP BY a.instrumentID, a.reading
ORDER BY a.instrumentID, a.reading




Vanderghast, Access MVP

 

[DKS]

Belated thanks. This does do the trick.

Is there a way to have only one line showing the max and min values as well
as the dates for the max and min values?

 

[vanderghast]

Many possible syntaxes, ... and speed of execution. Something like if:


SELECT instrumentID, MIN(reading) AS low, MAX(reading) AS high
FROM yourTableName
WHERE dateStamp BETWEEN startingDate AND endingDate
AND instrumentID = whichInstrument


is saved as q2, then

SELECT a.instrumentID, a.low, b.dateStamp, a.high, c.dateStamp
FROM (q2 AS a INNER JOIN yourTableName AS b
ON a.instrumentID = b.instrumentID AND a.low=b.reading)
INNER JOIN yourTableName AS c
ON a.instrumentID = c.instrumentID AND a.high=c.reading)



should do, in a reasonnable time of execution. Note that if many dateStamp
are possible for one min/max reading, ALL occurence of possible dates for
that reading will be listed (in a different record), so you may want to use,
instead:



SELECT a.instrumentID, a.low, MAX(b.dateStamp), a.high, MAX(c.dateStamp)
FROM (q2 AS a INNER JOIN yourTableName AS b
ON a.instrumentID = b.instrumentID AND a.low=b.reading)
INNER JOIN yourTableName AS c
ON a.instrumentID = c.instrumentID AND a.high=c.reading)
GROUP BY a.instrumentID, a.low, a.high


will give the latest date, only.



Vanderghast, Access MVP