How to calculate the time a user spent on a record from the audittrail?

[Datamulcher]

I am supervising a team of Coders performing data validations. I have a productivity database which calculates quantitative prod data, now I am looking to get it to calculate the amount of time a user spends on a case. I am thinking the way to do this would be a difference calculation using the audit trail, - avg("n amount of time spent per record").



How do I structure this?? I am still learning and I do not know how to define, from a query based on the audit trail, the idea of this record / next record. From there I would need to apply the difference calculation.

As an illustration:

Edits made as per audit trail by user X:

10:00am RecordID abc123
10:02am RecordID abc123
10:05am RecordID abc123
10:10am RecordID xxx000
10:00am RecordID xxx000

So I need some way to calculate "if RecordID <> previous RecordID then calculate difference in date/time between First of [RecordID]-abc123 and last of [RecordID]-abc123."

Currently the database is calculating a number of other variables, as shown below. I want to get some insight into quality, along the lines of "case with more edits performed in less time than the average overall = possibly more skilled user." To do this I need that difference calculation but have no idea how to do this, the calculation I understand but how to reference RecordID<>Previous RecordID and then apply the calculation- that's where I am confused.



Thank you for your help, you guys are a great resource!

[ranman256]

youd need something to capture the time when the user begins editing, then another on Save record.
do you have this in place?

[Datamulcher]

Hey Ranman, I have a "StartedWorking" query which pulls the First audit trail record for the User for the selected date (default of "Between date() and date()+1") - but not the First of the [RecordID]...
(Actually the time range is defined by a form control, so it is actually "Between [Forms]![frmProductivity]![TextDate] And DateAdd("d",1,[Forms]![frmProductivity]![TextDate])")

But - there are no "saves", the data validators are only performing EDITS so the equivalent of "save" would be a codingstatus = 2 (completed)
I could use time when the record is first accessed to the time that the codingstatus is set to 2 for that record. This still requires the record->nextRecord definition.

What would be the most simple way to find: First of [RecordID] within a set of [RecordID]s?
Then I could calculate the time difference between First of [RecordID] -> [codingstatus].[RecordID] = 2