rolling hours and sum of counts in 60 minutes

[jackjee]

Hi All
I have a data table in MS access (64bit) which is bus movements data. The attached Excel file is the sample data. I have tried to put it in excel file on what is expected result.
I want to create a crosstab query based on this data, and find rolling 60 minutes counts of bus movements. the rolling partition can be 5 minutes, and sum the count of movements when it reach 60 minutes. each 60 minutes can be the crosstab column heading.
I can create a crosstab with fixed hours such as 0000-0059, 0100-0159 etc, but can not figure out how to crosstab for rolling hours such as 0000-0100, 0005-0105,0010-0110,0015-0115 etc
Hope I explained well and expecting your expert support
Thank you in advance

[ADezii]

This may actually be easier to do in Excel than it is in Access. Is this a possibility?

[jackjee]

Hi ADezii
The data is in access DB and many other reporting is done from the db with this and other datasets.
That is why I requested for a crosstab query or can be a vba to populate another table based on this data. Hope i have explained well and you would be able to help me further

[ADezii]

Working on it, not an easy task.

[jackjee]

Thank you so much for offering me your kind support. Merry Christmas to you and family

[Rabbit]

I can't download your file but the high level overview is create a dummy table with the rolling ranges. Join this to your data. Do a crosstab on it.

[ADezii]

I am really having a tough time with this one, but I'll be happy to give you what I have so far. Basically, I was able to somewhat reproduce the Expected Results Spreadsheet by extracting various Time Components, comparing them against the Time Slots, and writing them to a Table. This approach is kludgy to say the least, but when I have more time I will look into it further. I could not get it to work via a Crosstab, so it is strictly a Code based approach. I would also venture to say that Rabbit is much more qualified in this area than I am, and I would be more inclined go with his solution since it will probably be a much better one. In any event, have a look at the Demo I created, and hopefully it will be of some value.

[jackjee]

Hi ADezii
Thank you for taking time to help me. I am so happy for the consideration you have shown during this holiday time.
I looked at the demo and for test purpose I changed the time in the last 2 record as 04:40 and 04:46 to see if the result table will populate a column include that hour, but when I click the form button, the last column remain same as 0150-0250.
In normal scenario, the table will hold records for any date with time from 00:00:00 until 23:59:59.
Hope you can guide me on how the code can be modified to take all the hours in a day.
Thank you for the support and kindness

[ADezii]

The Columns are not dynamically created but manually created Fields in a Table (tblTimeSlots), and herein lies the problem. If I am reading you correctly, there are 12, 5-minute, rolling Time Slots for each hour which would equate to 288 Time Slots. Access will only allow 255 Fields in any given Table, so you would basically have to create two Result Tables, one from 12:00 A.M. to 12:P.M., and the other for the remainder of the day. I am assuming that you want to display the 288 Time Slots, but perhaps I am wrong. Kindly advise. When I am free, I`ll Upload Demonshowing how you two new Time Slots can be displayed. Here is that Revision for you that will show how Times such as 04:40 and 04:46 can now be displayed, since the Time Slot of 04:40-05:40 was created in tblTimeSlots, and new Records added to tblBusSchedules .

[ADezii]

Here is another option that you wish to consider. It is easier just to have you Download the File and have a look at it. It needs some work, but is doable.

[jackjee]

Hi ADezii
Thank you for taking your valuable time on this.
Post#9, yes I was looking for that option with 288 columns, but if there is a limitation, I can go with the latest example you have provided in last reply which is populating in the 'Rolling Hours_2' DB.
My final goal is to get the timeslots populated dynamically based on the data. As per your last reply, hope it will be doable. Thank you for the kind support and will wait

[ADezii]

Now that the Option in Post# 10 is acceptable, I need to do a complete Revision. The 288 Time Slots will now become Records in a Time Slot Table. A Query will then pass each Time Slot (Field) to a Public Function which will return the appropriate Value for that Slot. Now, we will have a Table with the Bus Schedules, a Table with the 288 Time Slots, a Query to generate the results, and a Public Function that contains the actual Logic. With this Setup, there will be no restrictions whatsoever. All I need now is time to get this all together. Hope you are not in a rush.

[jackjee]

Hi ADezii
Thanks for the quick reply. I am not at all in hurry. I am so happy that there is a solution for my request. I will wait for your reply

[ADezii]

My final goal is to get the timeslots populated dynamically based on the data.

Below is part of the puzzle that will dynamically create the 288 Time Slots along with some sample Results. This will be used to populated a Time Slots Table.

Public Sub CreateTimeSlots()
Dim intCtr As Integer
Dim intCtr2 As Integer
Dim intRun As Integer

For intCtr = 0 To 23
  For intCtr2 = 0 To 55 Step 5
    intRun = intRun + 1
      Debug.Print Format(intCtr, "00") & ":" & Format$(intCtr2, "00") & "-" & _
                  IIf(intCtr + 1 = 24, "00", Format$(intCtr + 1, "00")) & ":" & _
                  Format$(intCtr2, "00")
  Next intCtr2
Next

MsgBox CStr(intRun) & " Time Slots created in the Format: [hh:nn-hh:nn].", _
       vbInformation, "Time Slot Creator"
End Sub

09:00-10:00
09:05-10:05
09:10-10:10
09:15-10:15
09:20-10:20
09:25-10:25
09:30-10:30
09:35-10:35
09:40-10:40
09:45-10:45
09:50-10:50
09:55-10:55
10:00-11:00
10:05-11:05
10:10-11:10
10:15-11:15
10:20-11:20
10:25-11:25
10:30-11:30
10:35-11:35
10:40-11:40
10:45-11:45
10:50-11:50
10:55-11:55

[Rabbit]

First of all, I question the need for something like this. Not only does Access have a limit of 255 columns, it's unwieldy for a user to view and usefully process data that is this wide.

Oftentimes, it's better to rethink how you want to present the data and restructure it so it's presented as a tall recordset or limit the width and paginate as required.

That being said, as an exercise, there are useful techniques you can learn from the attached solution. You can do this with a dummy table of time slots and a crosstab query.

To aid in understanding, I split the query into 2, one to preaggregate, and one to crosstab on the aggregate.