Grouping on dates different only by seconds

I have an update query where one of it's functions is to track changes made on a table.

The update query sometimes processes large amounts of data and takes a few seconds to run.  This means that the date/time stamp on the tracking table looks something like this:

9/2/2016 10:36:29 AM
9/2/2016 10:36:29 AM
9/2/2016 10:36:30 AM
9/2/2016 10:36:30 AM
9/2/2016 10:36:31 AM
9/2/2016 10:36:31 AM
9/2/2016 10:36:32 AM

I'd like to run a report on what changes have been made using this table.  However I'm having a hard time grouping on the change time because seconds are too specific and minutes are too vague.  

What I'd like to do is set up some kind of expression that captures ~10 second range, and group on that expression.  Does anyone have any ideas on how I could do this?

Note:  The 10 second range would have to be a dynamic moving range.  In my above example I wouldn't want to just set hard numbers like 11-20, etc.  In my above example I wouldn't want the 29 second changes to be grouped separate from the 30, 31, 32 second changes.
BugHuggerNoobAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil RussellTechnical Development LeadCommented:
So create a calculated column on that data that gives you the date time to just the precision you want.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
this works to get the grouping for every 10 records:
SELECT t.id, t.DateTimeField, CInt((SELECT count( a.id)  FROM MyTablename a WHERE a.id <= t.id)\10) AS RankGroup
FROM MyTablename AS t;

BUT it crashes Access (for me) when grouping by RankGroup in a report.

I did not really understand what you are after .. my suggestion is to create a field in the table to use for grouping. Then run an update query to fill it with 'moving' logic -- and that way, you can group by a field that is not calculated.
0
Neil RussellTechnical Development LeadCommented:
You mean something like.....

"So create a calculated column on that data that gives you the date time to just the precision you want."
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

BugHuggerNoobAuthor Commented:
Neilsr, your "solution" is terrible.  You either don't understand what I'm asking for or you're being willfully ignorant so you can come across as condescending.  No one asked you to post here.  If you're going to spend your time here being a jerk to me for asking a question or being a jerk to Crystal for actually trying to help me then I'll have no problem reporting you to the mods.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
re-reading your question, I see you want about a 10 second range ... best bet, imo, is to add field to group by then loop through a recordset and fill it. When looping, each time group is changed, store start time for group then count how many records are in the group and have a tolerance for the amount of time and amount of records in each group

If you don't want to add a field to the data table, you can create another table with the ID of the data table and the RankGroup that you join in to the RecordSource for the report.
0
Neil RussellTechnical Development LeadCommented:
I'm sorry that you feel that way.
I fail to see how the accepted solution is a fat lot different.  It is basically creating an extra column and doing a calculation to populate it OR creating a new table and populating that with a calculation based on your dates and storing that along with the key of your data rows.

You ASKED for solutions and my input was to give you a solution that would work very simply. There is only one individual who is bad mouthing here....
0
BugHuggerNoobAuthor Commented:
Then ask for clarification instead of being flippant.

There is no date/time format that will match what I need.  Changes happen on a timescale of about 15-30 seconds.  If I want to format as just seconds then I get the problem of chopped up groupings:

29
--
30
30
30
--
31

If I set a static range (like 10-19, 20-29) then it still gets chopped inappropriately

29
--
30
30
30
31

If I do minutes then I might merge unrelated groups.  It will also chop related groups on the minute mark

12
12
12
59
59
--
00

So, sorry, but "format your column better" isn't helpful advice and misunderstands the problem
0
als315Commented:
Can you give exact expected result? If data will be:
9/2/2016 10:36:11 AM
9/2/2016 10:36:11 AM
9/2/2016 10:36:12 AM
9/2/2016 10:36:13 AM
9/2/2016 10:36:14 AM
9/2/2016 10:36:14 AM
9/2/2016 10:36:15 AM
9/2/2016 10:36:29 AM
9/2/2016 10:36:29 AM
9/2/2016 10:36:30 AM
9/2/2016 10:36:30 AM
9/2/2016 10:36:31 AM
9/2/2016 10:36:31 AM
9/2/2016 10:36:32 AM
9/2/2016 10:36:49 AM
9/2/2016 10:36:49 AM
9/2/2016 10:36:50 AM
9/2/2016 10:36:50 AM
9/2/2016 10:36:51 AM
9/2/2016 10:36:51 AM
9/2/2016 10:36:52 AM
what is expected?
First (median, last?) of some sequental range?
9/2/2016 10:36:11 AM
9/2/2016 10:36:29 AM
9/2/2016 10:36:49 AM
Such calculations could take a lot of time on large tables
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I have not put all the logic you probably want in here because I am just guessing .. but got this code started for you:
Sub CalculateGroupsForRecords()
'160904 strive4peace
'create records to group data by
'ASSUMES you have a table called MyGroupz with:
'  ID = Long Integer to correspond to ID of MyTable
'  Grp = Long Integer to store a group number for the record

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   'set up Error Handler
   On Error GoTo Proc_Err
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
   Dim sSQL As String _
      , nGrp As Long _
      , dtmStartTimeGroup As Date _
      , dtmLastTime As Date _
      , iNumRecordsInGroup As Integer _
      , iMinSecondsPerGroup As Integer _
      , iMaxSecondsPerGroup As Integer _
      , iSwitchAfterSeconds As Integer _
      , iMinRecordsPerGroup As Integer _
      , iMaxRecordsPerGroup As Integer
      
   Dim db As DAO.Database _
      , rsTable As DAO.Recordset _
      , rsGroups As DAO.Recordset
      
   '---------------------------------- set these values as desired
   iMinSecondsPerGroup = 3
   iMaxSecondsPerGroup = 40
   iMinRecordsPerGroup = 3
   iMaxRecordsPerGroup = 20 'set to high number like 99 or delete this to ignore
   'change to new group if no values for this time
   'and group has the minimum records
   iSwitchAfterSeconds = 10
   '-----------------------------------------------
      
   Set db = CurrentDb
   'Change IDfield, MyTimeField, and MyTable
   'assumes MyID is an AutoNumber (Long Integer)
   sSQL = "SELECT MyID, MyTimeField FROM MyTable " _
      & " WHERE Not IsNull(MyTimeField) " _
      & " ORDER BY MyTimeField "
   
   Set rsTable = db.OpenRecordset(sSQL)
   
   'remove previous records from the group table
   'note: there are better ways to do this without deleting records
   ' as this will require compact/repair* on the database to get rid of bloat
   ' -- but this is easiest
   ' * only compact/repair on a database that has just been backed up
   sSQL = "DELETE g.* FROM MyGroupz;"
   db.Execute sSQL
   
   Set rsGroups = db.OpenRecordset("MyGroupz")
   
   nGrp = 1
   iNumRecordsInGroup = 0
   
   With rsTable
      dtmStartTimeGroup = !MyTimeField
      Do While Not .EOF
         'determine if new group needs to be started
         Select Case True
         Case DateDiff("s", dtmStartTimeGroup, !MyTimeField) > iMaxSecondsPerGroup _
            , DateDiff("s", dtmLastTime, !MyTimeField) > iSwitchAfterSeconds And iNumRecordsInGroup > iMinRecordsPerGroup _
            , iNumRecordsInGroup > iMaxRecordsPerGroup
            
            nGrp = nGrp + 1
            dtmStartTimeGroup = !MyTimeField
            iNumRecordsInGroup = 0
         End Select
         
         'add record to grouping table
         rsGroups.AddNew
         rsGroups!ID = !MyID
         rsGroups!Grp = nGrp
         rsGroups.Update

         'store time of current record
         dtmLastTime = !MyTimeField
         'increment number of records in group
         iNumRecordsInGroup = iNumRecordsInGroup + 1
         'go to next record
         .MoveNext
      Loop
      'close recordset
      .Close
   End With
   'release object
   Set rsTable = Nothing
   
   MsgBox "Done setting Grp for records", , "Done"
   
Proc_Exit:
   On Error Resume Next
   'release object variables if still open
   If Not rsTable Is Nothing Then
      rsTable.Close
      Set rsTable = Nothing
   End If
   If Not rsGroups Is Nothing Then
      rsGroups.Close
      Set rsGroups = Nothing
   End If
   Set db = Nothing
   Exit Sub
 
Proc_Err:
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
     & "   CalculateGroupsForRecords"
 
   Resume Proc_Exit
   Resume
End Sub

Open in new window

I have done this example using another table for grouping since that is more complex -- better would be to put Grp in your data table, add it to the SQL statement when recordset is opened, and do Edit instead of AddNew -- and obviously, you would not modify the ID field. Because date/time is stored as double, which is not precise, I do not trust linking on it.

If you do use this, perhaps you will post your code after editing so we can see what you are after? thank you
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Use the simple function below to round your time entries to ten seconds.

Then your query will follow this layout:

Select Max(SomeField) From YourTable
Group By DateRound([TimestampField], 0, 0, 10)

Open in new window


Public Function DateRound( _
  ByVal datDate As Date, _
  Optional ByVal intHours As Integer, _
  Optional ByVal intMinutes As Integer, _
  Optional ByVal intSeconds As Integer) _
  As Date
  
' Rounds datDate to hours and/or minutes and/or seconds as
' specified in parameters intHours, intMinutes, and intSeconds.
'
' Will accept any value within the range of data type Date:
'
'   From 100-01-01 00:00:00 to 9999-12-31 23:59:59
'
' In case the range is exceeded due to rounding, the min. or max.
' value will be returned.
'
' Examples:
'   DateRound(#9999-12-31 23:57:50#,0,5,0)
'     returns: 9999-12-31 23:59:59
'   DateRound(#9999-12-31 23:57:10#,0,5,0)
'     returns: 9999-12-31 23:55:00
'   DateRound(#9999-12-30 22:57:50#,0,5,0)
'     returns: 9999-12-30 23:00:00
'   DateRound(#2015-02-28 12:37:50#,0,15,0)
'     returns: 2015-02-28 12:45:00
'   DateRound(#2015-05-05 11:27:52#,3,0,0)
'     returns: 2015-05-05 12:00:00
'   DateRound(#2015-05-25 11:11:13#,0,0,2)
'     returns: 2015-05-25 11:11:14
'
'   Round to the tenth of a day:
'   DateRound(#2012-11-15 15:00:00#, 2, 24, 0)
'     returns: 2012-11-15 14:24:00
'
' 2008-04-16. Gustav Brock, Cactus Data ApS, CPH.
' 2010-05-12. Modified CDate expression.

  Dim datRounding As Date
  
  On Error GoTo Err_DateRound
  
  datRounding = TimeSerial(intHours, intMinutes, intSeconds)
  If datRounding <= 0 Then
    ' Round to the second.
    datRounding = TimeSerial(0, 0, 1)
  End If
  ' Apply CDec to prevent rounding errors from Doubles and allow large values.
  datDate = CDate(Int(CDec(datDate) / CDec(datRounding) + 0.5) * datRounding)
  
Exit_DateRound:
  DateRound = datDate
  Exit Function
  
Err_DateRound:
  If datDate < 0 Then
    datDate = #1/1/100#
  Else
    datDate = #12/31/9999 11:59:59 PM#
  End If
  Resume Exit_DateRound
  
End Function

Open in new window

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.