Crosstab query for time duration from 2 fields

**NeoPa** · Jun 20 '22, 02:02 AM

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.

**jackjee** · Jun 20 '22, 05:10 AM

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

Code:

Sub duplicateandaddtime()

Dim ws As Worksheet
Set ws = Sheets("test")
Dim ws2 As Worksheet
Set ws2 = Sheets("test2")
Dim StartRow As Long, LRow As Long, i As Long, j As Long

LRow = ws.Range("A" & ws.Rows.Count).End(xlUp).row
StartRow = 2 

For i = StartRow To LRow

    For j = 1 To ws.Range("A" & i).Offset(, 5).value
        LRow2 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1).row
                      
        ws2.Range("A" & LRow2).value = ws.Range("A" & i).value
        ws2.Range("B" & LRow2).value = ws.Range("B" & i).value
        ws2.Range("C" & LRow2).value = ws.Range("C" & i).value
        ws2.Range("D" & LRow2).value = ws.Range("D" & i).value
            
    Next j
    
Next i

End Sub

**NeoPa** · Jun 20 '22, 04:41 PM

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.

Code:

Private Sub DuplicateAndAddTime()
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim lngRow As Long, lngRow2 As Long
    Dim datFrom as Date, datTo as Date, datVar As Date
    Dim strVar As String

    Set ws = Sheets("test")
    Set ws2 = Sheets("test2")

    Call ws2.Select()
    'Option 1: Clear existing data in test2 worksheet.
    Call Range("2:" & Range("A1").End(xlDown).Row).EntireRow.Delete()
    lngRow2 = 1
    'Option 2: Append to existing data in test2 worksheet.
    lngRow2 = Range("A1").End(xlDown).End(xlDown).End(xlUp).Row
    For lngRow = 1 To ws.Range("A1").End(xlDown).Row
        datFrom = TimeValue(ws.Range("C" & lngRow))
        datTo = TimeValue(ws.Range("D" & lngRow))
        strVar = Format(datFrom, "HH:mm")
        Mid(strVar, 4, 2) = Format((CLng(Right(strVar, 2)) \ 5) * 5, "00")
        datStart = CDate(strVar)
        For datVar = datStart To datTo Step #00:05:00#
            lngRow2 = lngRow2 + 1
            Range("A" & lngRow2) = ws.Range("A" & lngRow)
            Range("B" & lngRow2) = ws.Range("B" & lngRow)
            Range("C" & lngRow2) = IIf(datVar < datFrom, datFrom, datVar)
            Range("D" & lngRow2) = datVar + #00:05:00#
        Next datVar
    Next lngRow
    Call ws.Select()
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.

**jackjee** · Jun 21 '22, 04:50 AM

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(Rig ht(strVar, 2)) \ 5) * 5, "00"). I changed it as Mid(strVar, 4, 2) = Format(CLng(Rig ht(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

**NeoPa** · Jun 21 '22, 07:59 PM

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.

**NeoPa** · Jun 21 '22, 08:06 PM

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.

**jackjee** · Jun 22 '22, 08:52 AM

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

Code:

Private Sub DuplicateAndAddTime()
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim lngRow As Long, lngRow2 As Long
    Dim datFrom As Date, datTo As Date, datVar As Date
    Dim strVar As String
 
    Set ws = Sheets("test")
    Set ws2 = Sheets("test2")
 
    Call ws2.Select
    'Option 1: Clear existing data in test2 worksheet.
    Call Range("2:" & Range("A2").End(xlDown).row).EntireRow.Delete
    lngRow2 = 1
    'Option 2: Append to existing data in test2 worksheet.
    lngRow2 = Range("A2").End(xlDown).End(xlDown).End(xlUp).row
    For lngRow = 2 To ws.Range("A2").End(xlDown).row
        datFrom = TimeValue(ws.Range("C" & lngRow))
        datTo = TimeValue(ws.Range("D" & lngRow))
        strVar = Format(datFrom, "HH:mm")
        Mid(strVar, 4, 2) = Format((CLng(Right(strVar, 2)) \ 5) * 5, "00")
        datStart = CDate(strVar)
        For datVar = datStart To datTo Step #12:05:00 AM#
            lngRow2 = lngRow2 + 1
            Range("A" & lngRow2) = ws.Range("A" & lngRow)
            Range("B" & lngRow2) = ws.Range("B" & lngRow)
            Range("C" & lngRow2) = IIf(datVar < datFrom, datFrom, datVar)
            Range("D" & lngRow2) = datVar + #12:05:00 AM#
        Next datVar
    Next lngRow
    Call ws.Select
End Sub

**NeoPa** · Jun 22 '22, 10:11 PM

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 :

Code:

    'Option 1: Clear existing data in test2 worksheet.
    lngRow2 = Range("A1").End(xlDown).End(xlDown).End(xlUp).Row
    If lngRow2 > 1 Then
        Call Range("2:" lngRow2).EntireRow.Delete()
        lngRow2 = 1
    End If

Option 2 (Lines #15-16) needs no change from my original.

Line #17
As mentioned above, this should be :

Code:

    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 :

Code:

GGT     rq1     20-01-22 05:56  20-01-22 06:11

Expected results :

Code:

GGT     rq1     20-01-22 05:56 20-01-22 06:00
GGT     rq1     20-01-22 06:00 20-01-22 06:05
GGT     rq1     20-01-22 06:05 20-01-22 06:10
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 :

Code:

Private Sub DuplicateAndAddTime()
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim lngRow As Long, lngRow2 As Long
    Dim datFrom as Date, datTo as Date, datVar As Date
    Dim strVar As String

    Set ws = Sheets("test")
    Set ws2 = Sheets("test2")

    Call ws2.Select()
    'Option 1: Clear existing data in test2 worksheet.
    lngRow2 = Range("A1").End(xlDown).End(xlDown).End(xlUp).Row
    If lngRow2 > 1 Then
        Call Range("2:" lngRow2).EntireRow.Delete()
        lngRow2 = 1
    End If
    'Option 2: Append to existing data in test2 worksheet.
    lngRow2 = Range("A1").End(xlDown).End(xlDown).End(xlUp).Row
    For lngRow = 2 To ws.Range("A1").End(xlDown).Row
        datFrom = TimeValue(ws.Range("C" & lngRow))
        datTo = TimeValue(ws.Range("D" & lngRow))
        strVar = Format(datFrom, "HH:mm")
        Mid(strVar, 4, 2) = Format((CLng(Right(strVar, 2)) \ 5) * 5, "00")
        datStart = CDate(strVar)
        For datVar = datStart To datTo Step #00:05:00#
            lngRow2 = lngRow2 + 1
            Range("A" & lngRow2) = ws.Range("A" & lngRow)
            Range("B" & lngRow2) = ws.Range("B" & lngRow)
            Range("C" & lngRow2) = IIf(datVar < datFrom, datFrom, datVar)
            Range("D" & lngRow2) = datVar + #00:05:00#
        Next datVar
    Next lngRow
    Call ws.Select()
End Sub

**CJ_London** · Jul 1 '22, 11:56 AM

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

Crosstab query for time duration from 2 fields

Crosstab query for time duration from 2 fields

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment