Subtracting data of one time scale from data of a different time scale

[Automattic]

Hey all,

I have one set of data that comes in 15 min intervals that I need to average into 1 hour blocks (tbltest_hourly.[sngpressure]) so that I can subtract it by another data set that's comes in 1 hr intervals (tbltest_cronos.[sngpressure]). The time field is dtmdate for both tables.

The two child tables (tbltest_hourly and tbltest_cronos) are related to each other through a parent table (tblsiteid) that hosts all of the site names.

How do I go about averaging the 15 min data (yyyy/mm/dd hh:nn) so that I can perform operations with the 1 hr data?

If necessary I can fix this in excel (it's a bit messier), before importing, but I was hoping to do this in access.

Thanks in advance,

-A

 

[SydneyGeek]

I did this using 2 queries. The first query grabs the hour that each data point arrives; the second groups and averages the result.

Based on tblTest with fields TimeStamp and Amount. TimeStamp is the field that records the time that you want to process.

First query, qryPartition:

SELECT tblTest.TimeStamp, tblTest.Amount, DateValue([TimeStamp]) AS RectDate, Int((TimeValue([TimeStamp])*24)) AS RectHour
FROM tblTest;

Second query, qryGrouping:

SELECT qryPartition.RectDate, qryPartition.RectHour, Avg(qryPartition.Amount) AS AvgOfAmount
FROM qryPartition
GROUP BY qryPartition.RectDate, qryPartition.RectHour;

Denis

 

[Automattic]

Very clever solution. I'll give it a shot on monday and get back to you.

Thank you

-A

 

[Automattic]

Hey Denis,

I tried out your solution and everything looked good. Since i'll also need to average by day at a later point, the queries you provided will be easily adaptable to the next scale.

Thank you very much,

-A

 

[SydneyGeek]

You're welcome

Denis