One way is to build and populate a table of time buckets, e.g.
----------------------------------------
create table timePeriods
(
startTime datetime not null primary key,
endTime datetime not null
)
----------------------------------------
you can use something like this to populate the 5-minute increments:
-----------------------------------------
Dim sqlString As String
Dim timeFrom As Date
Dim timeTo As Date
timeFrom = 0
Do While timeTo < #8:00:00 AM#
timeTo = DateAdd("n", 5, timeFrom)
sqlString = "insert into timePeriods(startTime, endTime) " _
& "values (#" & Format$(timeFrom, "hh:mm:ss") & "#,#" _
& Format$(timeTo, "hh:mm:ss") & "#)"
CurrentProject.Connection.Execute sqlString
timeFrom = timeTo
Loop
------------------------------------------
then join to this table in your query:
------------------------------------------
select t.startTime, t.endTime, r.InstrumentName,
Avg(r.Value) as avgValue
from timePeriods as t left join readings as r
on r.Time_and_Date>=t.startTime
and r.Time_and_Date<t.endTime
group by t.startTime, t.endTime, r.InstrumentName
order by t.startTime;
------------------------------------------
The above gives the 5-minute averages for each instrument so pop that
into a crosstab query and you're set. You'll need to figure out a way to
chop off the date portion from your date_and_time column values - I'll
leave that to you :)
ke*******@gmail.com wrote:
I have a database that stores data collected from a variety of
instruments (pressure, temperature, flow rate, etc.) everytime there
is a 1% change in the value.
The Project Manager wants to be able to take the data and generate a
report that shows a series of timed averages across a larger time
span. For example, over an eight hour time range, he wants the
instrument averages in five minute blocks.
00:00:01 - 00:05:00 Press1avg01 | Temp1avg01 | Flow1avg01 | etc...
00:05:01 - 00:10:00 Press1avg02 | Temp1avg02 | Flow1avg02 | etc...
00:10:01 - 00:15:00 Press1avg03 | Temp1avg03 | Flow1avg03 | etc...
00:15:01 - 00:20:00 Press1avg04 | Temp1avg04 | Flow1avg04 | etc...
...
07:50:01 - 07:55:00 Press1avg95 | Temp1avg95 | Flow1avg03 | etc...
07:55:01 - 08:00:00 Press1avg96 | Temp1avg96 | Flow1avg04 | etc...
The core data is stored in the following format:
Time_and_Date | InstrumentName | Value [Float]
I can handle generating an average across a single time span, but I am
a little baffled as to how to cleanly generate a sequence of average.
Especially, since the data is not logged on a specific time rate (e.g.
every five minutes), but on percent change.
Thanks in Advance,
Ken
