473,554 Members | 2,281 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

rolling hours and sum of counts in 60 minutes

24 New Member
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, 86 views)
Dec 23 '20 #1
31 5546
ADezii
8,834 Recognized Expert Expert
This may actually be easier to do in Excel than it is in Access. Is this a possibility?
Dec 23 '20 #2
jackjee
24 New Member
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 Recognized Expert Expert
Working on it, not an easy task.
Dec 24 '20 #4
jackjee
24 New Member
Thank you so much for offering me your kind support. Merry Christmas to you and family
Dec 24 '20 #5
Rabbit
12,516 Recognized Expert Moderator MVP
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 Recognized Expert Expert
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 New Member
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 Recognized Expert Expert
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, 49 views)
Dec 26 '20 #9
ADezii
8,834 Recognized Expert Expert
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, 46 views)
Dec 26 '20 #10

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

Similar topics

5
28429
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) bb=datetime.datetime(2006, 8, 3, 17, 59, 36, 46000) so c=bb-aa will be datetime.timedelta(5, 6339, 437000)
3
5565
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 figure for a number of flight times recorded in a datasheet form? and is there a way to display flight times greater than 24 hours i.e 26:20 for...
4
5991
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 to another if the program is given: a. starting place b. departure time in hours and minutes c. destination d. arrival time in hours and minutes
6
4227
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 with my problem. I am working with Access 2k / Win 2k. I have some experience and must have this figured out before Aug1. What i am tasked with...
9
6184
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 03.20 Using Format(,"hh:nn") does not do it.
3
1781
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 shutting off. I created a query that I added an expression - to figure out total time the irrigation system was running - it gave its answer in days -...
2
39563
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 minutes should show 4:15pm. Please help. Anna
6
2411
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 onTimer(self,event): current = self.mc.Tell() self.info_pos.SetLabel(" %i seconds" % (int(current)/1000)) self.slider.SetValue(current)
3
2690
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 entered in hours and minutes on the text box "txt_duration" and display it in the text box "txt_ETA"
0
7535
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8051
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7894
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6161
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5442
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5162
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3579
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3561
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
850
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.