Find highest 4 hours out of 24

[nvrwrkn]

Howdy!



I have a need to find the highest 4 consecutive hours out of 24, each hour field containing some value.

Output would look like 0400 to 0759 or 0500 to 0859.

Trying to find the largest 4 hour block of hours in a day where so many values occur during each hour.

I'm thinking something that has to do 24 groups, 1st group is 00 to 04, second 01 to 05, etc and the calc the total values found in each block, but I can't get my head around how to do it.

Any helps is appreciated!

[June7]

Use the TOP keyword.

Review
http://allenbrowne.com/subquery-01.html

[nvrwrkn]

sorry... I've been off for a few days.

As for the TOP keyword, my useage of it so far has been to find the TOP N in a sort. Unless I'm not seeing deeper into your meaning, using TOP will just show me the top 4 hours with the largest value in each hour. That could be 0400, 1700, 2100, 2200. What I was looking to do was find the largest consecutive block of 4 hours... basically like taking a look at the total for 00 to 04, then 01 to 05, then 02 to 06 and so on and then finding which of those consecutive 4 hour blocks has the largest combined values.

Did I not look far enough into what you meant using TOP?

As I write this I'm thinking I'm gonna have to do something ugly to accomplish this, like a ton of hitting the tables.

Can this be manipulated in recordsets somehow? Can I do this creating 24 recordsets using each 4 hour block or is there another way to look at the entire table 4 consecutive chunks at a time and advancing the starting block by 1 each time until I hit the 24th block?

Here's an example... if the chart's big enough (ok, it's LARGE), you can see that 06,07,08,09 is the largest block of 4 hours.

[June7]

Provide sample data for analysis. An Excel might be sufficient.

[nvrwrkn]

Table1.xls.zip


If I attached correctly the excel inside produces a similar chart based on HOUR, and a count of CountofData. The chart produced shows hours 06 to 09 with a total count of 16, making that 4 hour block the largest out of all other possible 4 hour blocks.

Basically the need is to find a 4 hour consecutive block of time where the most events ( or COUNT of data ) happen.

*not at work now so I'm just making up something quick for a visual

*another edit: it's very likely that there may be times when an hour is not represented in the table, meaning no event was found during that hour and there was no datacount entered for that particular hour.

[June7]

I think this will require VBA code using recordset, arrays, and looping structures. Something like:

Option Compare Database
Option Explicit
Option Base 1

Sub GetSum()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim dblSum As Double, i As Integer
Dim ary4(4), aryD()
rs.Open "SELECT * FROM Table1 ORDER BY ID;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
For i = 1 To 4
    ary4(i) = rs!CountOfData
    rs.MoveNext
Next
ReDim aryD(1)
While Not rs.EOF
    aryD(UBound(aryD)) = ary4(1) + ary4(2) + ary4(3) + ary4(4)
    ary4(1) = ary4(2)
    ary4(2) = ary4(3)
    ary4(3) = ary4(4)
    ary4(4) = rs!CountOfData
    rs.MoveNext
    ReDim Preserve aryD(UBound(aryD) + 1)
Wend
dblSum = aryD(1)
For i = 1 To UBound(aryD)
    If aryD(i) > dblSum Then dblSum = aryD(i)
Next
Debug.Print dblSum
End Sub

This should give you some idea of the difficulties involved.

[nvrwrkn]

sigh....time to study up on arrays then. Figured the fastest was a recordset but never had a need to use arrays before, so now it's back to the books.

Thanks much!