Extract minimum temperature readings for a specified range

[ClimateGuy]

Hi there,




I have the following table in an access dbase (refer to sample attachment) that holds temperature measurements (at ten minute intervals) from a series or weather stations. The table is described below:


FIELD, DESC
id, weather station id
temp, temperature reading of weather station
date_time, date/time field (read as: dd/mm/yyyy hh:mm)


I need to construct a query to extract the minimum temperature between the times 9.00am to 8.50am the following day (i.e. a 23hr50min range) for each day (or range) for each weather station. FYI each station (80 in total) has 5 months worth of ten minute temperature readings from 1/08/2011 to 30/11/2011. Can anyone help with this query for i can't figure this one out?

[pbaldy]

Create a query that returns those 3 fields plus a new one along the lines of:

CalcDate: IIf(TimeValue(date_time)<#9:00:00 AM#,DateValue(DateAdd("d",-1,date_time)),DateValue(date_time))

That should give you a calculated field that returns the current date for records starting at 9, the previous date for the earlier records. You can group on that field in a totals query to get the minimum temperature. If you also need the time, this type of thing:

http://www.baldyweb.com/LastValue.htm

[June7]

EDIT: Did not see pbaldy's suggestion before I posted so dumped my comments after testing his and discovering it does indeed work.

SELECT Min(Table1.temp) AS MinOftemp, IIf(TimeValue([date_time])<#12/30/1899 9:0:0#,DateValue(DateAdd("d",-1,[date_time])),DateValue([date_time])) AS CalcDate
FROM Table1
GROUP BY IIf(TimeValue([date_time])<#12/30/1899 9:0:0#,DateValue(DateAdd("d",-1,[date_time])),DateValue([date_time]));


However, dates in dd/mm/yyyy instead of Access mm/dd/yyyy convention might give you grief. Review this http://allenbrowne.com/ser-36.html

[ClimateGuy]

Thank you guys! This is exactly what i needed.

One more thing, if i needed to extract average temperature for every hour for each each station, what would be the query for this? As you may gather, i struggle writing aggregation queries that have anything to do with date/time.

Once again thanks!

[June7]

See if this is what you want:
SELECT Avg(temp) AS AvgOftemp, ID, DateValue([date_time]) AS ReadDate, Hour([date_time]) AS ReadHour
FROM Table1
GROUP BY ID, DateValue([date_time]), Hour([date_time]);

[ClimateGuy]

Thanks June 7! Perfect. I appreciate your help