Grouping records by time period

[Gareth Jones]

Hi all,

I have a table with a date/time and a name. If I have a record for example with John as the name and the time is 12:00, can I replace any further instances of John with N/A for the next hour? All I need is the 1st instance of each name for the following hour. I can then filter by excluding N/A to shrink the amount of records in the query.

12:00 John
12:01 Simon
12:03 John (needs to be replaced by N/A)
12:04 John (needs to be replaced by N/A)
12:05 Jack
13:03 John (This can remain as more than 1 hour since the previous instance of the name)
13:05 John (needs to be replaced by N/A as less than 1 hour since the one at 13:03)

The above query would then be 4 records in total instead of 7.

All I have done so far is being able to filter names that are immediately afterwards but not if there any record in the middle

MyName: IIf(DLookUp("[Name1]","Name_Table","[ID]=" & [ID]-1)=[Name1],"N/A",[Name1])

This works ok but it obviously does not filter all the records I need. Would appreciate if anyone has any suggestions if this is possible.

Thanks in advance.

Gareth

[twinnyfo]

Gareth,

It perhaps would be helpful if we understood what you are trying to do in the bigger scheme of things. I think there are ways of doing what you want to do, but there may be better ways of doing this if we understood why you needed such a grouping. There may be better ways of doing what you are trying to do, besides changing the names to "N/A".

[Gareth Jones]

Thanks for replying.

Its essentially to do with Overtime. If someone has been called for a problem, it is classed as a callout. If they get a few more calls within an hour of this initial call, then it is classed as the same problem/callout. If any further calls are received after an hour, the process starts again and the first of these new calls is classed as a callout.

The hour figure is not set in stone, however once the functionality works, this figure can always be changed.

Thanks

[twinnyfo]

One method for dealing with this situation, might be to check the status of the technician's most recent call out and see if it has been more than an hour since. If not, then don't add a new record. However, from a management standpoint, I would think you would want to keep track of the number of calls, as well as the actual time.

I have some ideas on how to work this, but I would also need to know how this information is going to be used, such as, is it just going to be printed on a report. Do you need to know about the multiple calls, but just calcluate pay based on the time span? The reason I ask, is that there may be value in maintaining the name of the technician in your Table, but identifying the records you want to work from in a different way. Remember, by changing the name to "N/A", in your example, they are all "John", but could just as well be Jack or Simon.

Forgive me for asking more questions, but I want to make sure we guide you down a good path, not just one that will get you to a solution.

[Gareth Jones]

I appreciate you taking the time to look into this. All the calls are kept in the master table so for the purpose of this query, they can be filtered out completely. For every name, whether its John or Simon etc, the same rule applies, only display the first callout, filter the next hour's worth out and then start the process again.

Thanks

[twinnyfo]

Gareth,

This will do what you want it to do.... My assumptions were a Table namees tblCallOutHistory with two fields: TimeStamp And Technician.

Public Function UpdateCallOuts()
On Error GoTo EH:
    Dim db As Database
    Dim rstNames As Recordset
    Dim rstCallOuts As Recordset
    Dim strSQL As String
    Dim dtmFirstCallout As Date
    Set db = CurrentDb()
    strSQL = "SELECT Technician " & _
        "FROM tblCallOutHistory " & _
        "GROUP BY Technician " & _
        "HAVING Technician <> 'N/A';"
    Set rstNames = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rstNames.RecordCount = 0 Then
        rstNames.MoveFirst
        Do While Not rstNames.EOF
            strSQL = "SELECT * " & _
                "FROM tblCallOutHistory " & _
                "WHERE Technician = '" & rstNames!Technician & "' " & _
                "ORDER BY TimeStamp;"
            Set rstCallOuts = db.OpenRecordset(strSQL, dbOpenDynaset)
            If Not rstCallOuts.RecordCount = 0 Then
                rstCallOuts.MoveFirst
                dtmFirstCallout = rstCallOuts!TimeStamp
                rstCallOuts.MoveNext
                Do While Not rstCallOuts.EOF
                    If DateAdd("n", -60, rstCallOuts!TimeStamp) > dtmFirstCallout Then
                        dtmFirstCallout = rstCallOuts!TimeStamp
                    Else
                        With rstCallOuts
                            .Edit
                            !Technician = "N/A"
                            .Update
                        End With
                    End If
                    rstCallOuts.MoveNext
                Loop
            End If
            rstNames.MoveNext
        Loop
    End If
    Exit Function
EH:
    MsgBox Err.Number & ":  " & Err.Description
    Exit Function
End Function

I made this a public function so I could test it. If you would rather delete the call outs that are all within one hour of the first call out, then replace lines 30-34 with the following:

                        With rstCallOuts
                            .Delete
                        End With

Some things to think about: While this code will work and do what you want it to do, you should be aware that this will cycle through all records in your table, every time you want to check the call out times. This is not a big deal if you only have a few records, but if you keep records for several years, this could become a burden. Then you might want to add a new field, perhaps "Verified" as a Yes/No Field which would get updated when the code is run, then you change the Select Statement to filter out any verified records, then it only cycles through the records it needs to.

Second, if your DB is properly normalized, which is should be if it is not already, then remember that the SELECT Statements referring to the technician should be pointing to an index in another table (tblTechnicians). This is a good practice to get into, especially in the long run as you continue to develop and expand your project (we always do....). For some initial details about DB normalization, look here.

Hope this helps, but keep in mind, there may be better ways to manage what you are trying to do. However, based on what you want and how you want to do it, this solution may be helpful.

[Rabbit]

Here is a SQL only solution. It was created in SQL Server but is portable to Access.

SELECT
    uname,
    MIN(dt) AS dt
 
FROM 
    (
        SELECT
            t1.uname,
            t1.dt,
            MIN(t2.dt) AS mdt
 
        FROM 
            #t t1
 
            LEFT JOIN #t t2
            ON    t1.uname = t2.uname AND
                t1.dt > t2.dt
 
        WHERE
            DATEDIFF(MINUTE, t2.dt, t1.dt) >= 60 OR
            t2.dt IS NULL
 
        GROUP BY
            t1.uname,
            t1.dt
    ) t
 
GROUP BY
    uname,
    mdt
 
ORDER BY
    dt

[twinnyfo]

So, Rabbit, as usual, you amaze me! Could you help us mere mortals understand the nature of your SQL statement, particularly as it pertains to the "t", "#t", "t1" and "t2" stuff?

I think, I kinda mostly understand the rest of your solution, but it is meaningless without understanding how you got there. Obviously the aliens abducted you several years ago and imparted some special knowledge to you....


;-)

[Rabbit]

Don't be amazed just yet lol. I just did some more testing and realized it only works with the given dataset. It stops working as soon as you add in another hour.

[Gareth Jones]

Thats brilliant thanks but for some reason I am getting no results back at the moment. Is it to be used in the criteria for the Technician field as in:
WHERE (((tblCallOuts.technician)=UpdateCallOuts()))

[twinnyfo]

Gareth,

The Function UpdateCallOuts() in Post #6 does the updating for you, and does not return any values, so cannot be used as a criteria for a SELECT Statement, as you have used it. It will do all the updating by itself, leaving you only a list of Technicians and "N/A" (or delete the records completely).

Hope this helps your understanding of the code.

However, if Rabbit gets his wicked code working, he may have a better solution. But, again, we don't know exactly how you are going to use your list of names, whether it is in the table itself or a report, or whatever.

[Rabbit]

I've tried but failed. It's doable with a cursor but Access SQL doesn't have cursors.

[Gareth Jones]

No wonder I couldnt get any values back :)

I tried just running the function itself and runs without any errors however does not appear to update the table at the moment

[twinnyfo]

Your table/field names are properly coded into your VBA? I tested it on a table based on your first post and it ran without a hitch.

[Gareth Jones]

I tried again after re-compiling and it works perfect. Thanks both for your help. Appreciate it :)

[twinnyfo]

Glad we could help! Hope you have a great day!