Return rolling 15 minute period min & max

[svankley]

I'm inserting into a table, a value every five (5) minutes. How can I query for the minimum & Maximum sustained 15 minute value for the day? This would be the three lowest & highest contiguous combined values for the requested 24 hour period (i.e. 10:10, 10:15 & 10:20), or whatever the case may be.

Maybe this will help

The data is:
10:00 10
10:05 12
10:10 32
10:15 34
10:20 12
10:25 23
10:30 17
10:35 31
10:40 12
10:45 12
10:50 0
10:55 3
11:00 7
11:05 23
11:10 54
11:15 34
11:20 65
11:25 23
11:30 21
11:35 9
11:40 65
11:45 13
11:50 17
11:55 23
12:00 62
12:05 12
12:10 19
12:15 23

So in this example the minimum running 15-minute total would the 10:50-11:00 15-minute period with a total of 10.
The maximum running 15-minute total would the 11:10-11:20 15-minute period with a total of 153.

So I would want returned MIN = 10 and MAX=153

Hope this helps.

This one has really got me stumped - any takers?? 8^)

Thanks in advance!!

Steve

 

[dhookom]

You can try a SQL syntax like

SELECT tblTimeValues.TheTime, (Select Sum(TheValue) FROM tblTimeValues T WHERE T.TheTime Between DateAdd("n",-5,tblTimeValues.[TheTime]) and  DateAdd("n",5,tblTimeValues.[TheTime])) AS TheTimeSum
FROM tblTimeValues;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[svankley]

Hmmm, how is this finding the Min & Max for any three contiguous values for the day??

 

[svankley]

The following works perfectly in SQL Server, but for the life of me, I can't get the syntax correct for Access!!

SELECT Min(NewVal) AS MinValue, Max(NewVal) AS MaxValue
FROM Select A.Flow + B.Flow + C.Flow As NewVal
From MinMaxTest A
Inner Join MinMaxTest B
On A.Date_Time = DateAdd(n, -5, B.Date_Time)
Inner Join MinMaxTest C
On B.Date_Time = DateAdd(n, -5, C.Date_Time)
AS A
WHERE MinMaxTest.Date_Time=Forms!F_Main!hidMonth & "/" & Forms!F_Main!hidDay & "/" & Forms!F_Main!hidYear;

 

[dhookom]

The sql that I provided should give the sum of all three contiguous dates for the time period. I left you with querying the min and max from the results.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]