Calculate DateDiff on Time Added Field

[athyeh]

Hi Folks,

I have a table with the following relevant fields: [Operator], [Time Added] where [Time Added] = Now().

What I am trying to accomplish is to create a query that goes through and, by Operator, calculate the difference (in seconds) between records added.

As an added enhancement I would also like to add a CASE?/WHEN?/WHERE? statement that stipulates if the time difference between the previous record added and the next is greater than 5 mins (300 seconds), then insert a standard time difference of 100 seconds.

All input and help is appreciated.

Thanks!

[rpeare]

you really do not want to do this in a query. It's going to involve using domain functions (Dsum, dlookup, etc) which are incredibly impractical and memory intensive in queries.

Are you opposed to doing this with VBA?

[athyeh]

you really do not want to do this in a query. It's going to involve using domain functions (Dsum, dlookup, etc) which are incredibly impractical and memory intensive in queries.

Are you opposed to doing this with VBA?

Thank you for your reply and not at all. Whatever the best option is.

You're going to have to speak slowly thougH

[rpeare]

can you give an example of your data (with the field names included) and what the 'correct' result is based on your process (i.e. include a 5 minute gap and what you want the final value to be)

[athyeh]

ID	Date	Data_ID	Operator	Process	Elapsed (secs)
1	3/27/2013 9:26	12	John Smith	Bang Nails	100
2	3/27/2013 9:27	21	John Smith	Bang Nails	43
3	3/27/2013 9:28	35	John Smith	Bang Nails	100
4	4/8/13 7:45 AM	43	Jane Smith	Cut Wood	222
5	4/8/13 7:48 AM	23	Jane Smith	Cut Wood	100
6	4/8/13 7:59 AM	111	Jane Smith	Cut Wood	100

Note:

Elapsed Time between ID 1 and 2 is truly 100 seconds.

Because there is no subsequent record after ID 3 for John Smith, the returned value is 100 seconds.

The time elapsed between ID 5 and 6 > 5 minutes, therefore a standard time of 100 seconds is returned for ID 5: Elapsed

ID 6 same as ID 3.

[rpeare]

Ok... first I changed your dataset to be more normalized

In other words the OPERATOR I replaced with a field called OPERATOR_ID (FK to an operators table with an autonumber fields as the PK)
I changed PROCESS to PROCESS_ID (same concept as the operator, but really irrelevant for this example, just my own neuroses)

this is the code I used:

Dim db As Database
Dim rst_People As Recordset
Dim iOperatorID As Long
Dim rst_Dates As Recordset
Dim dCurrDate As Date
Dim rst_Functions As Recordset
Dim dPrevTime As Date
Dim dCurrTime As Date
Dim iTimeDiff As Long

Set db = CurrentDb
Set rst_People = db.OpenRecordset("SELECT Operator_ID FROM tblTest GROUP BY Operator_ID")
rst_People.MoveFirst

Do While rst_People.EOF <> True
    iOperatorID = rst_People.Fields("Operator_ID")
    Debug.Print iOperatorID
    Set rst_Dates = db.OpenRecordset("SELECT format(Fun_Date, ""Short Date"") as CurrDate FROM tblTest WHERE (Operator_ID = " & iOperatorID & ") GROUP BY Format(Fun_Date, ""Short Date"")")
    rst_Dates.MoveFirst
    Do While rst_Dates.EOF <> True
        dCurrDate = rst_Dates.Fields("CurrDate")
        Debug.Print "    " & dCurrDate
        Set rst_Functions = db.OpenRecordset("SELECT * FROM tblTest WHERE Operator_ID = " & iOperatorID & " AND format(fun_Date, ""Short Date"") = #" & dCurrDate & "# ORDER BY Fun_Date DESC")
        rst_Functions.MoveFirst
        dPrevTime = rst_Functions.Fields("fun_date")
        Do While rst_Functions.EOF <> True
            dCurrTime = rst_Functions.Fields("fun_Date")
            iTimeDiff = DateDiff("s", dCurrTime, dPrevTime)
            rst_Functions.Edit
            If iTimeDiff = 0 Or iTimeDiff > 300 Then
                rst_Functions!elapsed = 100
            Else
                rst_Functions!elapsed = iTimeDiff
            End If
            rst_Functions.Update
            Debug.Print "        " & rst_Functions.Fields("fun_Date")
            Debug.Print "            " & iTimeDiff
            rst_Functions.MoveNext
            dPrevTime = dCurrTime
        Loop
        rst_Functions.Close
        rst_Dates.MoveNext
    Loop
    rst_Dates.Close
    rst_People.MoveNext
Loop
rst_People.Close

Set db = Nothing

I left some debug.print statements in there so you can open the immediate window and see some statements as it's progressing, you can also add some debug.print statements to see the SQL statements if you want.

What this does is cycles through all your individual operators represented in your table.
For each operator represented it cycles through each day the person was active
For each day the operator was active it cycles through their timestamped records in DESCENDING order, always evaluating the last record as 100 and each record prior to that one by a number of seconds = 100 if the value for the time difference is greater than 300 seconds and the actual time difference if it's less than 300 seconds.

[rpeare]

One other note, if you want this to only look at 'new' records (i.e. an elapsed time is non zero) you'd have to alter the code or have some way of identifying unprocessed records. The code as it stands will calculate your elapsed time on your entire data set every time it runs which may not be what you want.

[athyeh]

Wow that's impressive. Came back with a few errors.. going to need to gain some understanding of what's in front of me to debug.

[athyeh]

    Set rst_Dates = db.OpenRecordset("SELECT format(Fun_Date, ""Short Date"") as CurrDate FROM tblTest WHERE (Operator_ID = " & iOperatorID & ") GROUP BY Format(Fun_Date, ""Short Date"")")

Regarding this line of code right here, what is: Fun_Date?

[rpeare]

you have a field called DATE in your example. DATE is a reserved word in Access and will cause you problems if you do more programming. I altered the dataset to be Fun_Date (function date) rather than 'DATE' to avoid using a reserved word in my example.