Get a duration of an event with only one timestamp per row?

[benlinkknilneb]

I've got an excel spreadsheet report that my team creates... takes a lot of work to run the report each month. We aren't sure how widely it's being used... if we ask, users say "oh this is critical to us" but we've had mistakes go out before that took them *months* to catch. So... this month, I added a quick & dirty logging system to the spreadsheet that writes to a publicly-accessible location on our network whenever the users open/close the workbook, and whenever they move from one tab to another. Just to see what they're up to....

My records look like this:

3/20/2013 9:09:06 AM;bob;Open Workbook
3/20/2013 9:09:08 AM;bob;Tab 6
3/20/2013 9:09:18 AM;bob;Tab 3
3/20/2013 9:09:25 AM;bob;Tab 9
3/20/2013 9:09:55 AM;bob;Close Workbook

I've imported this information into an access table and I'd like to query it in a way that gives me this information:

bob; Tab 6; 0:00:10
bob; Tab 3; 0:00:07
bob; Tab 9; 0:00:30

Any ideas how I might pull this off without resorting to VBA?

Ben
Windows isn't the answer; it's the question. NO is the answer.

 

[MajP]

I assumed some names
Table name: UseLog
Fields
TimeStamp
PersonID
Event

SELECT 
 UseLog.PersonID, 
 UseLog.Event, 
 UseLog.TimeStamp, 
(Select Min(A.Timestamp) from UseLog as A where A.PersonID = UseLog.personID AND A.TimeStamp > Uselog.timeStamp) AS NextTimeStamp, 
 Format([NextTimeStamp]-[TimeStamp],"hh:nn:ss") AS Duration
FROM 
 UseLog
WHERE 
 ([UseLog].[Event] = "Open Workbook" Or [UseLog].[Event] = "Close Workbook") = False;

 

[Andrzejek]

You could, instead of Access, pull this data into Excel:

[pre]
A B C D
1 3/20/2013 9:09:06 AM bob Open Workbook
2 3/20/2013 9:09:08 AM bob Tab 6
3 3/20/2013 9:09:18 AM bob Tab 3
4 3/20/2013 9:09:25 AM bob Tab 9
5 3/20/2013 9:09:55 AM bob Close Workbook
[/pre]

and in cell D2 write a formula concantinating B2 & C2 & subtract date A1 from A2

Would that work for you?

Have fun.

---- Andy

 

[PHV]

My preferred way (no subquery):
SELECT A.theName, A.theAction, Format(A.theDate - Min(B.theDate), 'h:nn:ss') AS theDuration
FROM theTable AS A INNER JOIN theTable AS B ON A.theName = B.theName AND A.theDate < B.theDate
GROUP BY A.theName, A.theAction, A.theDate
ORDER BY A.theDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886