Calculated difference between same field, diff records? 1

[straybullet]

Have the following:

PropID EventDate EvtType
5 10/4/2005 CMA
5 10/10/2005 LDM
5 3/17/2006 LDR
5 3/17/2006 SIR
5 3/30/2006 AVL
5 3/30/2006 SIR
5 4/24/2006 PND_HC
5 6/26/2006 CLS


How do I (or is it even possible to do...) calculate the number of days between each event? How do I determine the "next record"?

For instance:

PropID EventDate EvtType DaysDiff
5 10/4/2005 CMA 0
5 10/10/2005 LDM 6
5 3/17/2006 LDR 158
5 3/17/2006 SIR 0
5 3/30/2006 AVL 13
5 3/30/2006 SIR 0
5 4/24/2006 PND_HC 25
5 6/26/2006 CLS 63


Let them hate - so long as they fear... Lucius Accius

 

[Remou]

Perhaps something like:
[tt]SELECT tblX.PropID,
tblX.EventDate,
tblX.EvtType,
(SELECT Top 1
EventDate
FROM tblX X
WHERE EventDate>tblX.EventDate) AS NextDate,
[NextDate]-[Eventdate] AS Diff
FROM tblX
ORDER BY tblX.EventDate;[/tt]

 

[straybullet]

Thank you very much! That makes sense now that I see it written out!
The only odd this is that the last entry for each PropID is grabbing 12/12/####, with #### being the same year as EventDate as NextDate?
So long as this is consistent, Im sure there's a way to accomodate it. I'll be experimenting with this at work tomorrow.

Let them hate - so long as they fear... Lucius Accius

 

[Remou]

The snippet above is just a rough example. It is always worth posting in Microsoft: Access Queries and JET SQL, Forum701, where, as you must know, there are some very sharp SQL people.