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.

[ADezii]

Hello Rabbit, hope you had a nice Holiday. I cannot run Access right now which is why I am asking this question instead of Downloading the DB. How are you getting around the 255 Column Limition? The OP needs the 288 Time Slots to be visible and a Running Count per every 5 minutes with overlapping 60 minute Interval Totals. I am switching the results to a Query displaying the 288 Time Slots than passing each Time Slot as an Argument in a Calculated Field to a Public Function for processing.

[ADezii]

@jackjee:
This Thread deals with Bus Movement Data. Probably a stupid question, but the buses run 24 hours a day?

[Rabbit]

I'm not working around the 255 limit, it just has a crosstab for the AM time slots. If the OP really wants to display such a wide table, they could create a similar crosstab for the PM time slots and put them in two extremely wide subforms for display purposes.

[ADezii]

I was able to display all 288 Time Slots in a Vertical fashion as Fields in a Table. A Query, based on this Table, will generate the Running Totals in a Calculated Field. I also had to compensate for the 12 Time Slots that spanned Midnight, namely: (23:00-00:00) to (23:55-00:55). If this approach is not acceptable to you, then I suggest that you go with Rabbit's solution, if it is, than I ask that you do the following:

1. Look the results and Code over with a fine tooth comb, and make sure that they are valid.
2. Execute the Code with a new set of Data (repopulate tblBusSchedules), and again, check the Output to make sure that it is exactly what you are looking for.
3. Get back to us with your results.

[jackjee]

Hi Rabbit
Sorry for delayed reply. I tried the provided DB with some sample records. only issue I noticed that when the time is 5,10 15 etc. then that record is not counted. I am attaching the DB with my sample data for your kind check. Please look at the timeslots 0105-0205 where the query results 6, but actual record count should be 7.
Also at 0155-0255 record count shows as 0 but it should be 1 and in 2145-2245 it shows 1 but actual count should be 2. In all these records the 'RouteTime' is the first part of timeslot (0105,0155,2145). Hope you would be able to support to fix this issue.
I have attached the DB with new sample data and also an Excel file with the same records if incase the DB not opens.

[jackjee]

Hi ADezii
I tried with the sample file I posted above for Rabbit, but the results are not populating after 2300 hr slots. Then I tried Rabbit solution and it has some small issues which I already mentioned.
The purpose of this DB is to store the date and times of buses which are arriving at a specific bus station. The query I am looking is to find the hours (not static hours such as 0000-0059, 0100-0159 etc but as I mentioned take 5 minutes rolling) when maximum number of buses arrived in any given 60 minutes of 1 day.

Based on that we can produce reports to show that which hour is the busiest and how many hours this max value occurred in the same date.
The actual data has departure times as well and once i figure out max for arrivals with the support from you both, I can try myself for the departures.
Hope my explanation provides more clarity.
So basically I don't need to store the hourly counts, but store only those hours when the sum of count of buses in the 5 minutes 12 time slices is max (which is rolling 60 minutes). Please let me know if it is not clear

[Rabbit]

@ADezii, the preaggregate query in my example produces the tall result. The crosstab produces the wide result.

@Jackjee, it looks like the TimeSerial function produces slightly different stored values compared to the DateAdd function that populated the time slots table. To fix this, you just have to run a one time update on the TimeStart and TimeEnd fields in the time slots table to update them to the TimeSerial version.

As for the wrapping time slots, it doesn't make sense to me that you would want to count arrivals that come at the start of the day to also count at the end of the day and vice versa. I feel like this would give people looking at the aggregate the wrong idea about the amount of traffic routing through.

However, if that is what you want, then you just need to update the aggregation query to additionally filter in those time slices where the slot start time is after the slot end time.

Also, your latest post would indicate that you don't need a wide view of the data at all. In which case, just use the tall view, it simplifies the aggregation task.

[ADezii]

I Imported your Data and made a couple of changes. I do believe that we are spot on now, but only you can let me know if this is True.

[jackjee]

Hi ADezii
It looks like the latest DB code is working fine. I changed couple of entries time and executed the code and the rsults are getting as expected, but need to try with more data which I will do tonight and let you know how it is

[jackjee]

Hi Rabiit
Thank you for the guidance to 'run a one time update on the TimeStart and TimeEnd fields in the time slots table', but I am not sure how to do that. And on the reply you have mentioned that 'count arrivals that come at the start of the day to also count at the end of the day and vice versa' which is not clear to me. I want the counting from 0000 until 2359 of the same date. So when the rolling reaches 2300-2359 midnight, it can stop. No need for 2305 to next day 0005, but yes, when we have multiple day's data, this may be useful. The last post from ADezii looks workable, but I need more testing. SO I want to try both solutions and hope will reach a final call

[Rabbit]

All I really meant was to run a basic update query to update those fields

[jackjee]

Hi Rabbit
Apologies if I am not getting it clear.
Do you mean to say that run an update query to update the 'TimeStart' and 'TimeEnd' field of 'TimeSlots' table as below syntax "UPDATE TimeSlots SET TimeSlots.TimeStart = TimeSerial(Hour([timestart]),Minute([timestart]),Second([timestart])), TimeSlots.TimeEnd = TimeSerial(Hour([TimeEnd]),Minute([TimeEnd]),Second([TimeEnd]));"

[Rabbit]

Yup, pretty much, only needs to be done once.

You don't need it after it's run. You can get rid of the query or keep it for posterity, up to you.

[jackjee]

Hi ADezii
Apologies for delayed reply. Happy New year.
I was on off days and back to office. I am trying with the 'Rolling Hours_4' DB and now the challenge is I have multiple days data so I need to add the date in one field for the corresponding rolling hours (288 records) in the query 'qryGetTotals' which gives the final result.
The date will be available in the 'timeat' field of the main table. Hope my explanation is clear to understand or please advise so I can provide more clarity on my current scenario
Thank you for the kind support

[ADezii]

Hello jackjee, Happy New Year to you also. I won't be able to get to this until tomorrow, but I need to know exactly what I am getting at. Are you saying that for each unique Date (Time not factored in), there needs to be Values for the 288 Overlapping Time Slots. Three Dates would generate 864 Records?

[ADezii]

I am going to be busy for a couple of days, so I made a couple of assumptions and created the following Revision for you. It consists of the same Base Data, but I added two additional dates with exactly the same Time Components as the Original Data. This makes it easy to compare the Running Time Slot Totals for the three days since they should be almost the same. Look it over with a fine tooth comb, and I'll be back in a day or two to check in. Good Luck with your Project.

[jackjee]

Hi ADezii
Thank you for the support even though in your busy schedules. I had a quick look and it looks fine. Hope this will work for me. I will try with actual data and confirm soon