Need help with Access query to average groups of numbers

[stephentimko]

I have clusters of temperatures and humidity readings that need to be
averaged.

Most are three temperature and humidity readings within about six
minutes of each other. Some are four and some are just one or two.

What I need is an Acess query that will take those within a certain
window, say six minutes, and average the temperatures and average the
humidity.

I've got the date, the time, the temperature and the humidity as
separate fields.

 

[John W. Vinson]

I have clusters of temperatures and humidity readings that need to be
averaged.

Most are three temperature and humidity readings within about six
minutes of each other. Some are four and some are just one or two.

What I need is an Acess query that will take those within a certain
window, say six minutes, and average the temperatures and average the
humidity.

I've got the date, the time, the temperature and the humidity as
separate fields.

A Totals query with criteria to select the time range should work. For
example

SELECT Avg([temperature]), Avg([Humidity])
FROM yourtable
WHERE [date] = #2/11/2009# AND [time] BETWEEN #11:30# AND #11:36#;

You don't describe what constitutes "a cluster" or how Access should be able
to identify it; do you need that capability, i.e. to look at a whole set of
readings and figure out which belong in a cluster together?

 

[Ken Sheridan]

Are your 6-minute windows discrete or do they roll forward? If the former,
i.e. starting at midnight and with each window starting at 6 minute intervals
after that you could return the start time of the window for any date/time
value with the following function:

Public Function TimeWindowStart(dtmDateTime As Date) As Date

Dim dtmDateTimeTemp As Date

dtmDateTimeTemp = dtmDateTime

Do Until DatePart("n", dtmDateTimeTemp) Mod 6 = 0
dtmDateTimeTemp = DateAdd("n", -1, dtmDateTimeTemp)
Loop

TimeWindowStart = Format(dtmDateTimeTemp, "yyyy-mm-dd hh:nn")

End Function

You can then call the function in a query like so:

SELECT TimeWindowStart([ReadingDate]+[ReadingTime])
AS [TimeWindowStarting],
AVG([Temperature]) As [AverageTemperature],
AVG([Humidity]) As [AverageHumidity]
FROM [T&HReadings]
GROUP BY TimeWindowStart([ReadingDate]+[ReadingTime]);

If the windows roll forward, i.e. the first starts at midnight and ends at
00:05:59, the second starts at 00:01:00 and ends at 00:06:59 and so on then
you'd need to group by each rolling window, which would mean joining two
instances of the table to bring in all other date/times within 6 minutes of
each row's date/time value:

SELECT R1.[ReadingDate]+R1.[ReadingTime] AS [TimeWindowStarting],
AVG(R2.[Temperature]) As [AverageTemperature],
AVG(R2.[Humidity]) As [AverageHumidity]
FROM [T&HReadings] AS R1 INNER JOIN [T&HReadings] AS R2
ON DATEDIFF("n",R1.[ReadingDate]+R1.[ReadingTime],
R2.[ReadingDate]+R2.[ReadingTime]) < 6
GROUP BY R1.[ReadingDate]+R1.[ReadingTime];

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

On second thoughts make the query for the 'running averages' as follows:

SELECT R1.[ReadingDate]+R1.[ReadingTime] AS [TimeWindowStarting],
AVG(R2.[Temperature]) As [AverageTemperature],
AVG(R2.[Humidity]) As [AverageHumidity]
FROM [T&HReadings] AS R1 INNER JOIN [T&HReadings] AS R2
ON (DATEDIFF("n",R1.[ReadingDate]+R1.[ReadingTime],
R2.[ReadingDate]+R2.[ReadingTime]) BETWEEN 0 AND 5)
GROUP BY R1.[ReadingDate]+R1.[ReadingTime];

Ken Sheridan
Stafford, England

 

[stephentimko]

Thanks to everyone for the help.
The times are variable. I'll give this a try next week when I get
home.

 

[stephentimko]

I tried this and I get this error message:
The syntax of the subquery in this expression if incorrect.
Check the subquery's syntax and enclose the subquery in parentheses.