Trying to subtract date in record from date in "previous record" - I am failing hard. 1

[keun]

There are many ways to do this and I have tried a bunch. None of them are working.

This was my latest attempt and, while close, is not getting me what I want. 1) I am getting a few null results that I cannot reconcile and 2) The "DifCalc" result is formatted as a date - I want hh:mm:ss. Or just minutes.

Here is what I have settled on (among many other options):

SELECT sheet1.userid,
sheet1.action,
sheet1.datetime,

   (SELECT TOP 1 Dupe.DateTime                 
   FROM sheet1 AS Dupe                     
   WHERE Dupe.userid = sheet1.userid 
     AND Dupe.datetime < sheet1.datetime   
   ORDER BY Dupe.datetime DESC, Dupe.userid)          AS DifCalc
FROM sheet1;

Here is what my table looks like in Access. You can see that when the data is sorted by DateTime the users get intermingled and I am interested in the DateTime field calculation within each user. So in the data below, id001 does a Search at 0:00:22, I want to know the time that passed between that and his View Search Results at 0:00:26.

here is what the result is looking like:

Any and all help is appreciated. I am open to other approaches also. (FWIW my DateTime data has milliseconds, so I Am going to need to deal with that but I am taking this one step at a time. I can save the milliseconds for another question once I solve this one.)

I joined this forum in 2005. I am still a hack.

 

[PHV]

What about this ?

SELECT S.userid,
S.action,
S.DateTime,
Format(S.DateTime-(SELECT Max(D.DateTime) FROM sheet1 D WHERE D.userid=S.userid AND D.DateTime<S.DateTime),'h:nn:ss') AS DifCalc
FROM sheet1 S

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

 

[keun]

Yup, that's it. I hadn't asked the question correctly but with a minor change I got exactly what I need!

SELECT S.userid,
S.action,
S.DateTime,
Format(([b]SELECT [u]Min[/u](D.DateTime)-S.DateTime[/b] FROM sheet1 D WHERE D.userid=S.userid AND D.DateTime[b]>[/b]S.DateTime),'h:nn:ss') AS DifCalc
FROM sheet1 S

THANKS!

I joined this forum in 2005. I am still a hack.