I have copied the sample data that you have given into an Excel Sheet and transferred to Access as a table. Added a new field grp (Numeric)to the table. Created an Index with Date and Time Fields.
Written a VBA Routine to scan through the Date & Time fields and gives a group sequence Number in the grp field for records that falls within the time range 08:00 to 07:59 (on the same day or next day).
The VBA Routine is given below:
Code:
Public Sub Grouping()
Dim db As Database, rst As Recordset
Dim counter As Integer, T1 As Date, T2 As Date
Set db = CurrentDb
Set rst = db.OpenRecordset("Sheet11", dbOpenTable)
rst.Index = "DateTime"
counter = 0
T1 = rst![xDate] + rst![Time]
If rst![Time] < TimeValue("08:00") Then
T2 = rst![xDate] + TimeValue("08:00")
Else
T2 = rst![xDate] + TimeValue("08:00") + 1
End If
Do While Not rst.EOF
counter = counter + 1
Do While T1 < T2 And Not rst.EOF
rst.Edit
rst![Grp] = counter
rst.Update
rst.MoveNext
If Not rst.EOF Then
T1 = rst![xDate] + rst![Time]
End If
Loop
If Not rst.EOF Then
If rst![Time] < TimeValue("08:00") Then
T2 = rst![xDate] + TimeValue("08:00")
Else
T2 = rst![xDate] + TimeValue("08:00") + 1
End If
End If
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
Once all the records have the grouping sequence Numbers (the Table Datasheet View image after run of the program is attached) you can create a Total Query GROUP on the group-sequence number and take Count of each group. Sample SQL is given below:
Code:
SELECT Sheet11.grp, Count(Sheet11.grp) AS CountOfgrp
FROM Sheet11
GROUP BY Sheet11.grp;
The Query output image is attached.
If the data file have large amount of records it may take few minutes to complete the process.
The program is tested only with the sample data that you have provided. If the code needs further refinement you may do so.
Windows XP 
Access 2010 32bit
Reply With Quote
