Condense minutes to hour averages?

  • Thread starter Thread starter zeta
  • Start date Start date
Z

zeta

Hello,

I have a table of data with a data point for each minute
of the year. I would like to condense this data into
hourly averages. Is there a clever way to do this with a
query? Could a pivot table do this?

My table looks like this:

Date Hour Minute ValueX
---- ---- ------ ------
1/1/03 2 5 125.3
1/1/03 2 6 126.4
etc....

TIA
Zed
 
Just create a query that groups by the date and hour and averages ValueX.
Then when you want to use this average refer to the query instead of the
original table. You could also do the same to find hourly max and min,
which may also be of interest.

Kelvin
 
Ah yes, "group by", that is what i needed.
Thanks
-----Original Message-----
Just create a query that groups by the date and hour and averages ValueX.
Then when you want to use this average refer to the query instead of the
original table. You could also do the same to find hourly max and min,
which may also be of interest.

Kelvin




.
Click to expand...
 
You could simply gorup by the Date and hour fields and average the ValueX
field

e.g.

SELECT Test.Date, Test.Hour, Avg(Test.ValueX) AS AvgOfValueX
FROM Test
GROUP BY Test.Date, Test.Hour;

Just out of interest, why not save 2 fields of storage in your table and
only store the Date including the time?

Your database would be smaller (especially after a year you will have 525600
records), but you might have to do a bit more work when running queries.

Example:

Table:

SampleDate ValueX
1/1/2003 10:05:00 125.3
1/1/2003 10:06:00 126.4

To group by an hour, you would use the following query:


SELECT CDate(Format([Test].[Date],"dd/mm/yyyy")) AS [Date],
DatePart("h",[Test].[Date]) AS [Hour],
Avg(Test.ValueX) AS AvgOfValueX

FROM Test
GROUP BY CDate(Format([Test].[Date],"dd/mm/yyyy")),
DatePart("h",[Test].[Date]);

Hope this helps,

Trev.
 
Back
Top