473,320 Members | 1,732 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.

Crosstab query for time duration from 2 fields

24 16bit
Hi All
I have a table with records having timein and timeout from terminal. I am trying to find count of the buses in 5 minutes buckets of the hour for each terminal.

data sample:
Bus terminal Timein Timeout
GGT rq1 20-01-22 05:56 20-01-22 06:11
CDC rq1 20-01-22 05:58 20-01-22 06:16
HFT rq2 20-01-22 06:18 20-01-22 06:39
ABC rq3 20-01-22 06:42 20-01-22 06:46
REQ rq2 20-01-22 06:56 20-01-22 06:58

Expected results:
That means rq1 will have count as:
0601-0605 0606-0610 0611-0615 0616-0620
rq1 2 2 2 1
similar for rq2 and rq3 to show count of buses where there is in and out time fall in the bucket of 5 minutes

So the column heading of crosstab should be like
06:01-06:05 06:06-06:10 06:11-06:15 06:16-06:20 06:21-06:25 06:26-06:30 06:31-06:35 06:36-06:40 06:41-06:45 06:46-06:50 06:51-06:55 06:56-07:00

Hope someone can provide an answer on how to do a crosstab or vba to get the results
Jun 18 '22 #1
9 13451
NeoPa
32,556 Expert Mod 16PB
Hi Jack.

This looks complicated and fiddly, so will require an amount of work from you. However, the concept of checking for a time overlap for any time period is explained in Time Interval Overlapping (MS Access 2003).

NB. As you have it displayed in your OP you will have problems. Five minute intervals start immediately after the previous one finishes so 0616-0620 is not going to do it for you. You need 0610-0615 followed by 0615-0620. I hope that makes sense, because without that you'll be getting lots of wrong data.

In the circumstances you're wanting the overlap checks should be >= the start but < the end. Best of luck.
Jun 20 '22 #2
jackjee
24 16bit
Thank you for the quick reply.
the time intervals I am looking is basically minutes rounded, actual data will have time with seconds as hh:mm:ss.
The column headings are basically count from eg. 06:00:00 to 06:04:59 and next column start as 06:05:00 to 06:09:59.
So the count will be anything between this buckets, That is the reason I referred as 06:00-06:05 then 06:06-06:10
As you mentioned this will be complicated, is there a way we can split the record to multiple records for each 5 minute from the timeout field and then it will be easy to make crosstab based on timein column only.
I tried to do some preliminary work on the data in Excel to duplicate the records based on timeout such as:
added a column to get the duration from timein and timeout and get the count of how many 5 minutes (basically dividing the duration with 5)
Based on this value I create duplicate entries, but I don't know how to assign 5 minutes to the timein of the second and subsequent duplicate entries. If that is possible then I just need to run the crosstab based on the timein field. Any direction to do the time add will be great help
Below excel vba is used to create the duplicate entries

Expand|Select|Wrap|Line Numbers
  1. Sub duplicateandaddtime()
  2.  
  3. Dim ws As Worksheet
  4. Set ws = Sheets("test")
  5. Dim ws2 As Worksheet
  6. Set ws2 = Sheets("test2")
  7. Dim StartRow As Long, LRow As Long, i As Long, j As Long
  8.  
  9. LRow = ws.Range("A" & ws.Rows.Count).End(xlUp).row
  10. StartRow = 2 
  11.  
  12. For i = StartRow To LRow
  13.  
  14.     For j = 1 To ws.Range("A" & i).Offset(, 5).value
  15.         LRow2 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1).row
  16.  
  17.         ws2.Range("A" & LRow2).value = ws.Range("A" & i).value
  18.         ws2.Range("B" & LRow2).value = ws.Range("B" & i).value
  19.         ws2.Range("C" & LRow2).value = ws.Range("C" & i).value
  20.         ws2.Range("D" & LRow2).value = ws.Range("D" & i).value
  21.  
  22.     Next j
  23.  
  24. Next i
  25.  
  26. End Sub
  27.  
Jun 20 '22 #3
NeoPa
32,556 Expert Mod 16PB
Hi Jack.

I know WHY you listed them inappropriately. My point was that you had. It really doesn't matter whether you count seconds or minutes, it's not appropriate to list five minute increments by anything other than five minutes and treat the top end as less than. It's a mistake that many people fall over and no more helpful to those trying to read the results than it is to those (like yourself) trying to design them. My advice is still to do it the simple way and make your life easier but, as always, what you end up doing is absolutely your choice.

Obviously, we have to start again from scratch anyway, as you post an Excel question in the Access forum without the small detail of mentioning that the table you're referring to is an Excel Table rather than an Access one, which would generally be inferred, quite logically, from your original question. We do have an Excel forum here on Bytes.com, but we're generally happy to deal with Excel questions here in the busier Access forum as long as the question is clear about what it is. It saves wasting time you see.

Please understand, though we're still happy to help (Now we know what the situation is after all and we actually don't expect everyone to get everything right first time - it would be a very disappointing life if we did.), we are not here to do it all for you. It seems clear that isn't what you're after to be fair, even if the question is pretty broad in scope overall. I mention it because I won't be giving full solutions here - simply answering your specific questions.

If you want to normalise your data, as suggested above but with code to illustrate that doesn't really do that then, within your loop, you need to keep both the first two columns the same (GGT, rq1; CDC, rq1; REQ, rq2; etc) but then use something like a For loop to populate pairs of Date/Time values into subsequent rows.

This may be easier to do by processing each source row, from start to finish, based on each possible time slot in order. However, this leaves your connected data split. Fine for a database & processing - less fine for human reading & checking. Thus I will look instead at processing each input row to completion before proceeding to the next instead. I will also assume the more complicated situation where you don't know in advance what range of slots the times should fit into.

Expand|Select|Wrap|Line Numbers
  1. Private Sub DuplicateAndAddTime()
  2.     Dim ws As Worksheet
  3.     Dim ws2 As Worksheet
  4.     Dim lngRow As Long, lngRow2 As Long
  5.     Dim datFrom as Date, datTo as Date, datVar As Date
  6.     Dim strVar As String
  7.  
  8.     Set ws = Sheets("test")
  9.     Set ws2 = Sheets("test2")
  10.  
  11.     Call ws2.Select()
  12.     'Option 1: Clear existing data in test2 worksheet.
  13.     Call Range("2:" & Range("A1").End(xlDown).Row).EntireRow.Delete()
  14.     lngRow2 = 1
  15.     'Option 2: Append to existing data in test2 worksheet.
  16.     lngRow2 = Range("A1").End(xlDown).End(xlDown).End(xlUp).Row
  17.     For lngRow = 1 To ws.Range("A1").End(xlDown).Row
  18.         datFrom = TimeValue(ws.Range("C" & lngRow))
  19.         datTo = TimeValue(ws.Range("D" & lngRow))
  20.         strVar = Format(datFrom, "HH:mm")
  21.         Mid(strVar, 4, 2) = Format((CLng(Right(strVar, 2)) \ 5) * 5, "00")
  22.         datStart = CDate(strVar)
  23.         For datVar = datStart To datTo Step #00:05:00#
  24.             lngRow2 = lngRow2 + 1
  25.             Range("A" & lngRow2) = ws.Range("A" & lngRow)
  26.             Range("B" & lngRow2) = ws.Range("B" & lngRow)
  27.             Range("C" & lngRow2) = IIf(datVar < datFrom, datFrom, datVar)
  28.             Range("D" & lngRow2) = datVar + #00:05:00#
  29.         Next datVar
  30.     Next lngRow
  31.     Call ws.Select()
  32. End Sub
NB. This is air-code. IE. Not tested or debugged. If you can't get it to work then please explain very clearly what is wrong with reference to the line number(s) displayed.
Jun 20 '22 #4
jackjee
24 16bit
Dear NeoPa
Thank you for the guidance and time you are giving.
My apologies about the time interval interpretation and happy to take your advise to keep the counts for 0600-0605, 0605-0610 etc.
To get the counts in that way, I dont know how to deal with my data which has 2 time fields and as I was not sure how to deal with this from my Access Table, I thought I will do the record split from Excel and then I can import the data to Access Table. So there ill be only 1 time field to consider for the crosstab. My final goal is to get the counts for each 5 minutes. I can do a 1 hour column heading in Access crosstab query, but still i dont know how to do the 5 minutes column headings. I am aware that there will be column count limitations in Access and may not be able to put all 5 minutes of 24hours (which will be 288 column) in access query, but at least if someone guide me how to do 1 or 2 hours 5 minutes column headings that will be also ok.
Thank you for the Excel vba code but when I run the above code in Excel it gives me error on line 21 and I think there is a close bracket in wrong place.
Mid(strVar, 4, 2) = Format(CLng(Right(strVar, 2)) \ 5) * 5, "00"). I changed it as Mid(strVar, 4, 2) = Format(CLng(Right(strVar, 2) \ 5)) * 5, "00")
After correcting the bracket and run the code, another error popped up on line 29 (Next datStart ) as 'Invalid Next control variable reference'.
I am not sure how to correct it. Hope you would be able to guide me on this and help me to get the final result
Jun 21 '22 #5
NeoPa
32,556 Expert Mod 16PB
Hi Jack.

Thank you for pointing out the compilation problems clearly and precisely. That's a great help. I have amended my original copy with the required updates and will explain where each was wrong.

I'll start with the Next line :
This should match the variable used in the For line. It was changed as I was working on it as the variable names can be critical when working with code - especially as it gets more complicated.

The line with the brackets also needed to be changed. Not the way you guessed, and I'll explain why :
Breaking it down, it starts as {...} = Format({...}, "00"), where {...} stands for something we don't care about at this point ;-) The second {...} is further broken down as {X} \ {Y} * {Z}. Now, typically, being mathematically literate, we would expect (See BODMAS & BIDMAS.) to operate on which order these mathematical operations are performed such that it would be equivalent to ({X} \ {Y}) * {Z}. However, the \ appears to have a lower order level than the *, so we need to specify that explicitly to avoid {X} \ ({Y} * {Z}) which is certainly not what we want. In short, my corrected version above (or below depending on your settings) is what you need.

At this stage I suggest you get that part working before we proceed onto fine-tuning what you have left. It's perfectly possible that the results you see will help you to some clearer thinking on where to go from that particular point.

As to the Access / Excel issue, as I say - as long as we know then we're fine to do either - certainly most of us here. That said, had you wanted to keep it all in Access that might have been a little easier to code. Not by much. The references to the data may have been a bit easier (Recordset references instead of Worksheet Range ones.) but as you seem comfortable in Excel anyway then this way's fine.

Good luck, and let us know where you get to once you have the code working and giving you a set of data you can work with.
Jun 21 '22 #6
NeoPa
32,556 Expert Mod 16PB
Hi Jack.

I should add - purely for completeness and even if you know this already there may be many who read the thread later who don't - that the \ operator is an Integer Division operator. This means (in X \ Y) that X would be divided by Y, but only the whole numbers (integers) of Y would be counted. EG. 32 / 5 = 6.4 but 32 \ 5 = 6. I originally expected that this would be processed in the order based on it being like * or /, but it seems that is not the case and it's done after all those are completed. Hence the parentheses are necessary to force the order required.
Jun 21 '22 #7
jackjee
24 16bit
Thank you so much for the detailed explanation and finding time to correct the code.
I have tested the new code and got some errors then did some finetuning as the data has column headings. The actual records start from Row2.
changed cell reference to A2 wherever A1 for sheet("test") mentioned. Also 'For lngRow = 1 To....' changed as 'For lngRow = 2 To...'
Now the records are duplicated with 5 minutes interval, but I have a small problem there as:
1 - I need to keep the datetime format in the results such as 20-01-22 06:11
2 - I need to keep the first record with same times and then the second record onwards (when a duplicate is going to create) for the same record the next duplicate to have column C time as 5 minutes added and so on until it reaches the D column time.
Same logic will continue for all records in the range. Hope I explained well
For example,
GGT time in Column C as 20-01-22 05:56 and column D 20-01-22 06:11
So the duplicate entries for CGT will be as:
GGT 20-01-22 05:56 and column D 20-01-22 06:11
GGT 20-01-22 06:01 and column D 20-01-22 06:11
GGT 20-01-22 06:06 and column D 20-01-22 06:11
GGT 20-01-22 06:11 and column D 20-01-22 06:11


Expand|Select|Wrap|Line Numbers
  1. Private Sub DuplicateAndAddTime()
  2.     Dim ws As Worksheet
  3.     Dim ws2 As Worksheet
  4.     Dim lngRow As Long, lngRow2 As Long
  5.     Dim datFrom As Date, datTo As Date, datVar As Date
  6.     Dim strVar As String
  7.  
  8.     Set ws = Sheets("test")
  9.     Set ws2 = Sheets("test2")
  10.  
  11.     Call ws2.Select
  12.     'Option 1: Clear existing data in test2 worksheet.
  13.     Call Range("2:" & Range("A2").End(xlDown).row).EntireRow.Delete
  14.     lngRow2 = 1
  15.     'Option 2: Append to existing data in test2 worksheet.
  16.     lngRow2 = Range("A2").End(xlDown).End(xlDown).End(xlUp).row
  17.     For lngRow = 2 To ws.Range("A2").End(xlDown).row
  18.         datFrom = TimeValue(ws.Range("C" & lngRow))
  19.         datTo = TimeValue(ws.Range("D" & lngRow))
  20.         strVar = Format(datFrom, "HH:mm")
  21.         Mid(strVar, 4, 2) = Format((CLng(Right(strVar, 2)) \ 5) * 5, "00")
  22.         datStart = CDate(strVar)
  23.         For datVar = datStart To datTo Step #12:05:00 AM#
  24.             lngRow2 = lngRow2 + 1
  25.             Range("A" & lngRow2) = ws.Range("A" & lngRow)
  26.             Range("B" & lngRow2) = ws.Range("B" & lngRow)
  27.             Range("C" & lngRow2) = IIf(datVar < datFrom, datFrom, datVar)
  28.             Range("D" & lngRow2) = datVar + #12:05:00 AM#
  29.         Next datVar
  30.     Next lngRow
  31.     Call ws.Select
  32. End Sub
  33.  
Jun 22 '22 #8
NeoPa
32,556 Expert Mod 16PB
Hi Jack.

To start with let's be clear my code was designed to handle header rows in both sheets. It wasn't quite right as line #17 should have started at 2 not 1, but changing A1 to A2 is generally not appropriate. Bear in mind that, while we can assume test1 will always have data down to at least Row #2 (No point in running the process otherwise.), test2's data may start with 1 (just the header), 2 or more Rows. Using .End(xlDown) (equivalent to Ctrl-DownArrow) has a very different result depending on whether you're already at the bottom of a set of data or either within it or at the top.

Before we look at each individually I should explain that there are two blocks of code offered & which you choose depends on whether your desire is to clear out any existing data in test2 before adding the new data from test1 (Lines #12-14), or to add new rows to any existing data you already have (Lines #15-16). You need to choose one set and delete the other depending on what your requirement actually is.

Option 1 - Lines #12-14 :
My original needs to be changed - but not the change you've tried. The original works fine except that it should only be applied if there is data to be deleted. It would also delete the header line if there was no data. If using this section then replace with :
Expand|Select|Wrap|Line Numbers
  1.     'Option 1: Clear existing data in test2 worksheet.
  2.     lngRow2 = Range("A1").End(xlDown).End(xlDown).End(xlUp).Row
  3.     If lngRow2 > 1 Then
  4.         Call Range("2:" lngRow2).EntireRow.Delete()
  5.         lngRow2 = 1
  6.     End If
Option 2 (Lines #15-16) needs no change from my original.

Line #17
As mentioned above, this should be :
Expand|Select|Wrap|Line Numbers
  1.     For lngRow = 1 To ws.Range("A1").End(xlDown).Row
Your suggested change would actually cause it to crash on you in some circumstances.

Now, I'm struggling to follow your explanations but I think I get that you are not, after all, looking for times of the day but are instead interested in times within history, such that you need both the date & the time portions of the data. I'll look at changing that for you when you've got the most recent changes suggested above working as expected.

Another point at the bottom of your post indicates you're still struggling to understand how to group the data. Taking your first example line from your OP (Original Post) the data I would expect to see - after it's been converted to using full date data and not separating out the times - would be as below. Your posted expectation seems to have entirely the wrong time boundaries as well as missing the second column from the original data. If you don't want / need that column then that needs to be understood clearly before writing code to produce what you want.
Original line of data :
Expand|Select|Wrap|Line Numbers
  1. GGT     rq1     20-01-22 05:56  20-01-22 06:11
Expected results :
Expand|Select|Wrap|Line Numbers
  1. GGT     rq1     20-01-22 05:56 20-01-22 06:00
  2. GGT     rq1     20-01-22 06:00 20-01-22 06:05
  3. GGT     rq1     20-01-22 06:05 20-01-22 06:10
  4. GGT     rq1     20-01-22 06:10 20-01-22 06:11
NB. For ease of use I will repost the updated code here as the most up-to-date version :
Expand|Select|Wrap|Line Numbers
  1. Private Sub DuplicateAndAddTime()
  2.     Dim ws As Worksheet
  3.     Dim ws2 As Worksheet
  4.     Dim lngRow As Long, lngRow2 As Long
  5.     Dim datFrom as Date, datTo as Date, datVar As Date
  6.     Dim strVar As String
  7.  
  8.     Set ws = Sheets("test")
  9.     Set ws2 = Sheets("test2")
  10.  
  11.     Call ws2.Select()
  12.     'Option 1: Clear existing data in test2 worksheet.
  13.     lngRow2 = Range("A1").End(xlDown).End(xlDown).End(xlUp).Row
  14.     If lngRow2 > 1 Then
  15.         Call Range("2:" lngRow2).EntireRow.Delete()
  16.         lngRow2 = 1
  17.     End If
  18.     'Option 2: Append to existing data in test2 worksheet.
  19.     lngRow2 = Range("A1").End(xlDown).End(xlDown).End(xlUp).Row
  20.     For lngRow = 2 To ws.Range("A1").End(xlDown).Row
  21.         datFrom = TimeValue(ws.Range("C" & lngRow))
  22.         datTo = TimeValue(ws.Range("D" & lngRow))
  23.         strVar = Format(datFrom, "HH:mm")
  24.         Mid(strVar, 4, 2) = Format((CLng(Right(strVar, 2)) \ 5) * 5, "00")
  25.         datStart = CDate(strVar)
  26.         For datVar = datStart To datTo Step #00:05:00#
  27.             lngRow2 = lngRow2 + 1
  28.             Range("A" & lngRow2) = ws.Range("A" & lngRow)
  29.             Range("B" & lngRow2) = ws.Range("B" & lngRow)
  30.             Range("C" & lngRow2) = IIf(datVar < datFrom, datFrom, datVar)
  31.             Range("D" & lngRow2) = datVar + #00:05:00#
  32.         Next datVar
  33.     Next lngRow
  34.     Call ws.Select()
  35. End Sub
Jun 22 '22 #9
CJ_London
27 16bit
Appreciate this post is a week old and may now be resolved, but this could be done in an Access query fairly easily. I'm thinking link the two excel tables in Access then export the result back to Excel.

Happy to provide the code if this is of interest
Jul 1 '22 #10

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

Similar topics

8
by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I...
3
by: Mat N | last post by:
Hi, I've been trying to work out how to create a report based on crosstab query for which the number of fields is variable. For example in a situation where you show customer billing by year in...
4
by: Judy | last post by:
I'm using Access 2003 and was wondering if it is possible to have a paramater selection within a crosstab query so that I wouldn't need to build a new table. I have a select query that I'm using...
14
by: Tina | last post by:
My employer tracks productivity/performance of clinicians (how much they bill) each week, its averages for the month, and the 6 months. These averages are compared to their expected productivity....
6
by: tizmagik | last post by:
I am having a lot of difficulty generating a CrossTab Query based report. I have looked online for several tutorials and whatnot but I have not been able to really find what I'm looking for, nor...
2
by: Keith Wilby | last post by:
I have a report that is based upon a crosstab query which return only the columns (fields) it has data for. When my report runs it sometimes fails because some of the text boxes don't have a field...
4
by: m.wanstall | last post by:
I have a crosstab query that compiles data for Months of the year. I have a stacked select query on top of that crosstab query that uses the latest 2 months data and exports it to a fixed length...
5
by: HowHow | last post by:
First time using crosstab query because tired of creating queries and put them together in one query (such a stupid thing to do :D). Not sure how it works still. I have link table called...
1
by: rajeevs | last post by:
Hi All I have a table with field names as username, Position,login,logout. Login, logout fields data type is date and time (dd-mm-yy hh:nn) I wish to generate a crosstab query with a 30 minute...
1
by: jackjee | last post by:
Hi I have a continuous form based on crosstab query results. The fields in the form is from the crosstab column headings. I am looking for updating the records directly from the form such as on...
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...
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)...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.