Solved

Grouping on dates different only by seconds

Posted on 2016-09-02
10
39 Views
Last Modified: 2016-09-08
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.
0
Comment
Question by:BugHugger
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 41781828
So create a calculated column on that data that gives you the date time to just the precision you want.
0
 
LVL 19
ID: 41781851
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 41781854
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
 

Author Comment

by:BugHugger
ID: 41781867
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
 
LVL 19
ID: 41781872
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 37

Expert Comment

by:Neil Russell
ID: 41782047
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
 

Author Comment

by:BugHugger
ID: 41782086
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
 
LVL 39

Expert Comment

by:als315
ID: 41783696
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
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41783732
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41783758
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now