Calculating time difference from the record above

[gsrai31]

I need help to calculate time difference from the record above where ID is same. I have attached an example in excel with this request where I have if statement doing exacly what I want to do in Access. I have a table in Access that has three columns - as below

ID, Reg, TimeDiff
66646, 14/06/2007 21:33,
66646, 15/06/2007 06:03, 8:30:00
66646, 15/06/2007 12:20, 6:17:00
66646, 15/06/2007 15:08, 2:43:00
67844, 14/06/2007 04:39,
67844, 14/06/2007 09:05, 4:26:00
67844, 14/06/2007 09:08, 0:03:00
67844, 14/06/2007 16:27, 4:09:00

If ID is equal to the ID in record above then TimeDiff is equal to reg minus reg in the above recored, else blank.

Hope someone can help me with this.

Thanks, GS

 

[pbaldy]

Something along these lines?

http://www.access-programmers.co.uk/forums/showthread.php?t=138016

 

[gsrai31]

Paul, based on your query, mine looked as the one below and I got the exact results what I was looking for.

SELECT tblTag.ID, tblTag.Reg, (SELECT Reg FROM tblTag AS Alias WHERE Alias.ID = tblTag.ID AND Alias.Reg = (SELECT Max(Reg) FROM tblTag AS Alias2 WHERE Alias2.ID = tblTag.ID AND Alias2.Reg < tblTag.Reg)) AS PrevReg, Format([Reg]-[PrevReg],"hh:nn")
AS TimeDiff
FROM tblTag
ORDER BY tblTag.ID, tblTag.Reg;

Thank you for your help.
GS

 

[gsrai31]

Paul
I am having problems with this query. It runs fine if my table has only few records. But if I have about 1000 records in the table, I get a message that “At most one record can be returned by this subquery”. Is there a way to overcome this problem?
Thanks for your help with this.
GS

 

[EMP]

Try this query.

SELECT tblTag.ID, tblTag.Reg,
(Select Max(Reg) from tblTag as S where S.ID=tblTag.ID and S.Reg<tblTag.Reg) AS PrevReg,
Format(Reg-PrevReg,"hh:nn") AS TimeDiff
FROM tblTag;

^

 

[gsrai31]

Excellent, it worked !!!

Many Thanks to you and Paul for help.