rolling hours and sum of counts in 60 minutes

[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.TimeS tart = TimeSerial(Hour ([timestart]),Minute([timestart]),Second([timestart])), TimeSlots.TimeE nd = 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?