473,320 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

rolling hours and sum of counts in 60 minutes

24 16bit
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
Attached Files
File Type: xlsx sample_rollingHours.xlsx (10.3 KB, 84 views)
Dec 23 '20 #1

✓ answered by 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.

31 5532
ADezii
8,834 Expert 8TB
This may actually be easier to do in Excel than it is in Access. Is this a possibility?
Dec 23 '20 #2
jackjee
24 16bit
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
Dec 24 '20 #3
ADezii
8,834 Expert 8TB
Working on it, not an easy task.
Dec 24 '20 #4
jackjee
24 16bit
Thank you so much for offering me your kind support. Merry Christmas to you and family
Dec 24 '20 #5
Rabbit
12,516 Expert Mod 8TB
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.
Dec 24 '20 #6
ADezii
8,834 Expert 8TB
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.
Attached Files
File Type: zip Rolling Hours.zip (24.4 KB, 66 views)
Dec 24 '20 #7
jackjee
24 16bit
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
Dec 26 '20 #8
ADezii
8,834 Expert 8TB
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.
Attached Files
File Type: zip Rolling Hours_Revised.zip (24.6 KB, 48 views)
Dec 26 '20 #9
ADezii
8,834 Expert 8TB
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.
Attached Files
File Type: zip Rolling Hours_2.zip (26.1 KB, 45 views)
Dec 26 '20 #10
jackjee
24 16bit
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
Dec 27 '20 #11
ADezii
8,834 Expert 8TB
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.
Dec 27 '20 #12
jackjee
24 16bit
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
Dec 27 '20 #13
ADezii
8,834 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub CreateTimeSlots()
  2. Dim intCtr As Integer
  3. Dim intCtr2 As Integer
  4. Dim intRun As Integer
  5.  
  6. For intCtr = 0 To 23
  7.   For intCtr2 = 0 To 55 Step 5
  8.     intRun = intRun + 1
  9.       Debug.Print Format(intCtr, "00") & ":" & Format$(intCtr2, "00") & "-" & _
  10.                   IIf(intCtr + 1 = 24, "00", Format$(intCtr + 1, "00")) & ":" & _
  11.                   Format$(intCtr2, "00")
  12.   Next intCtr2
  13. Next
  14.  
  15. MsgBox CStr(intRun) & " Time Slots created in the Format: [hh:nn-hh:nn].", _
  16.        vbInformation, "Time Slot Creator"
  17. End Sub
Expand|Select|Wrap|Line Numbers
  1. 09:00-10:00
  2. 09:05-10:05
  3. 09:10-10:10
  4. 09:15-10:15
  5. 09:20-10:20
  6. 09:25-10:25
  7. 09:30-10:30
  8. 09:35-10:35
  9. 09:40-10:40
  10. 09:45-10:45
  11. 09:50-10:50
  12. 09:55-10:55
  13. 10:00-11:00
  14. 10:05-11:05
  15. 10:10-11:10
  16. 10:15-11:15
  17. 10:20-11:20
  18. 10:25-11:25
  19. 10:30-11:30
  20. 10:35-11:35
  21. 10:40-11:40
  22. 10:45-11:45
  23. 10:50-11:50
  24. 10:55-11:55
Dec 27 '20 #14
Rabbit
12,516 Expert Mod 8TB
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.
Attached Files
File Type: zip Database1.zip (764.4 KB, 47 views)
Dec 27 '20 #15
ADezii
8,834 Expert 8TB
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.
Dec 27 '20 #16
ADezii
8,834 Expert 8TB
@jackjee:
This Thread deals with Bus Movement Data. Probably a stupid question, but the buses run 24 hours a day?
Dec 27 '20 #17
Rabbit
12,516 Expert Mod 8TB
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.
Dec 28 '20 #18
ADezii
8,834 Expert 8TB
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.
Attached Files
File Type: zip Rolling Hours_3.zip (25.2 KB, 64 views)
Dec 28 '20 #19
jackjee
24 16bit
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.
Attached Files
File Type: zip Database2_Rabbit.zip (158.0 KB, 44 views)
File Type: xlsx Sample1.xlsx (16.5 KB, 71 views)
Dec 28 '20 #20
jackjee
24 16bit
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
Dec 28 '20 #21
Rabbit
12,516 Expert Mod 8TB
@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.
Dec 28 '20 #22
ADezii
8,834 Expert 8TB
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.
Attached Files
File Type: zip Rolling Hours_4.zip (34.1 KB, 71 views)
Dec 28 '20 #23
jackjee
24 16bit
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
Dec 29 '20 #24
jackjee
24 16bit
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
Dec 29 '20 #25
Rabbit
12,516 Expert Mod 8TB
All I really meant was to run a basic update query to update those fields
Dec 29 '20 #26
jackjee
24 16bit
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]));"
Dec 29 '20 #27
Rabbit
12,516 Expert Mod 8TB
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.
Dec 29 '20 #28
jackjee
24 16bit
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
Jan 6 '21 #29
ADezii
8,834 Expert 8TB
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?
Jan 6 '21 #30
ADezii
8,834 Expert 8TB
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.
Attached Files
File Type: zip Rolling Hours_5.zip (46.7 KB, 47 views)
Jan 7 '21 #31
jackjee
24 16bit
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
Jan 7 '21 #32

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Lad | last post by:
Hello, what is the best /easest way how to get number of hours and minutes from a timedelta object? Let's say we have aa=datetime.datetime(2006, 7, 29, 16, 13, 56, 609000)...
3
by: Nigel Heald | last post by:
Hi Folks, We have a form that records flight times in hours and minutes, for example a 1 hour 15 minute flight is recorded as 1:15 Does anyone know how to get Access 2003 to calculate a total...
4
by: patsandy | last post by:
Hi, I am Patricia and I would like to get some assistance to write a Program in C. Your help would be greatly appreciated. The Program must calculate how long it takes to fly from one place...
6
by: richbneal | last post by:
I really like the site so far and this is my first post. I have looked through some of the archives with no luck. I have also read the posting guidelines and will do my best to be clear and accurate...
9
by: Jim Devenish | last post by:
I have a numeric field (double) which holds a number of hours. I wish to display this on a report in the form of hours and minutes. E.g. 2.5 to be displayed as 02:30 1.25 as 01:15 3.2 as...
3
by: mulchgirl | last post by:
I have a table where I have fields name START DATE and SHUT OFF DATE with the data type at Date/Time and it's format set to General Date so that I can record both date and time of starting and...
2
Rozeanna Jerry
by: Rozeanna Jerry | last post by:
Hi every one..I am trying to add 8 hours 15 minutes to current time and show the new time on a text box. How would I do that.? For example if the current time is 8:00am then adding 8 hours 15...
6
prettypython
by: prettypython | last post by:
Hi i want to know how to define the seconds into hours and minutes for the media player timer i have already done it in seconds but not sure how to hours and minute. Pls help! ef...
3
by: miraheem | last post by:
hi, i have 3 text boxes the first one is names as: txt_tof the second one: txt_duration the third one: txt_ETA now i need to add the time entered in the text box "txt_tof" with the value...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.