Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Grouping on dates different only by seconds

Posted on 2016-09-02
10
Medium Priority
?
59 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 23
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 23
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
 
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 40

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 23

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 2000 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 52

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

824 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