Displaying timestamps corresponding to min / max values

[DKS]

I have a table with temperature readings of different locations over a period
of time. Thus multiple records per location.

I have a query to show me the min and max temperatures for each location.

I needed to add to that query the date when the min and the max temperatures
were recorded. How could I do that?

Many thanks in anticipation.

 

[John Spencer]

This query returns two records (one for the minimum at the location and one
for the maximum)
SELECT YourTable.Location, Temperature
IIF(YourTable.Temperature = YourCurrentQuery.MinTemperature,
"Minimum","Maximum") as Which
FROM YourTable INNER JOIN YourCurrentQuery
ON YourTable.Location = YourCurrentQuery.Location
WHERE YourTable.Temperature = YourCurrentQuery.MinTemperature
OR YourTable.Temperature = YourCurrentQuery.MaxTemperature

You could probably use a crosstab query if you wanted both in one row. I
think that might look something like the following (but no guarantee on this
as I usually mess up a cross tab query at least once when I build them)

TRANSFORM First(Temperature) as TheTemp
SELECT YourTable.Location
FROM YourTable INNER JOIN YourCurrentQuery
ON YourTable.Location = YourCurrentQuery.Location
WHERE YourTable.Temperature = YourCurrentQuery.MinTemperature
OR YourTable.Temperature = YourCurrentQuery.MaxTemperature
GROUP BY YourTable.Location
PIVOT IIF(YourTable.Temperature = YourCurrentQuery.MinTemperature,
"Minimum","Maximum")

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