query

[MLT]

I have a table with 3 columns: Thermometer_ID, Time_of_Reading_,
Value_of_Reading. The thermometers broadcast a time and temperature
reading every 5 minutes into this database. I'm trying to analyze the
data by Thermometer_ID. For example, the maximum temperature at
Thermometer #1. I can do this pretty easily with a query. Where I
get lost is when I want to be able to display in the results what time
the particular reading occured.

I think what might be happening is that when I query just [group by]
Thermometer_ID and [maximum] Value_of_Reading, that works great, but
when you throw Time_of_Reading into the query, it ends up just
returning all the values in the original table as all maximums because
every Value_of_Reading has its own unique Thermomter_ID and
Time_of_Reading.

Any suggestions?

Thanks!

 

[Duane Hookom]

Try something like:

SELECT A.*
FROM tblWith3Columns A
WHERE A.Value_of_Reading =
(SELECT Max(B.Value_of_Reading)
FROM tblWith3Columns B
WHERE A.Thermometer_ID =B.Thermometer_ID);

 

[John Spencer]

You need a sub-query to identify the maximum temperature per thermometer and
then you link that back to your table using the max temperature and the
thermometer id. Something like the following query.

SELECT Temperatures.*
FROM Temperatures INNER JOIN
(SELECT Thermometer_ID, Max(Value_of_reading) as HighPoint
FROM Temperatures
GROUP BY ThermometerID) as GetMax
ON Temperatures.Thermometer_ID = GetMax.Thermometer_ID
AND Temperatures.Value_Of_Reading = GetMax.HighPoint

This probably will work well for you if you have only one day's worth of
readings. If you have multiple days and want the max for each day you will
need to add in the dates to your relationship.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Ray]

The following gets you close. But if a given thermometer has multiple ties
for max, it will return them all.

SELECT T.Thermometer_ID, T.MaxReading, Time_of_Reading
FROM (Select Thermometer_ID, MAX(Value_of_Reading) As MaxReading
FROM TableName
GROUP BY Thermometer_ID) T
LEFT OUTER JOIN TableName ON T.Thermometer_ID = TableName.Thermometer_ID AND
T.MaxReading = TableName.Value_of_Reading


The following will just return the most recent max:

SELECT T.Thermometer_ID, T.MaxReading, T2.MostRecentReading
FROM (Select Thermometer_ID, MAX(Value_of_Reading) As MaxReading
FROM TableName
GROUP BY Thermometer_ID) T
LEFT OUTER JOIN
(Select Max(Time_of_Reading) AS MostRecentReading FROM TableName GROUP BY
Thermometer_ID, Value_of_Reading) T2

 

[MLT]

This is good info, however I'm not very familiar with using SQL for
access. Are there any recommended beginner's tutorials out there?

 

[Duane Hookom]

http://lmgtfy.com/?q=Access+SQL+beginner+tutorial