Query showing Date between record and previous record

[emorris1000]

I have a table of records that shows a series of events. Each record represents 1 event with 1 date given to it. I need to know the time between that event and the event that occured directly before it.

So like record 1 would be 1/1/2001. Record 2 would be 1/5/2001. Record 3 would be 1/25/2001. The query would return "4 days" (or whatever format) for record 2 and "20 days" for record 3.

I've done something similar to this before with an SQL query that did a numerical integration, but I can't believe that is necessary here.

 

[pbaldy]

You can use a technique like this to get the value from the previous record:

http://allenbrowne.com/subquery-01.html#AnotherRecord

From there it's a simple DateDiff() to get the days (or just subtract).

 

[emorris1000]

Ah ok, yeah that's along the lines of what I did for the integration query. Its an ugly little piece of sql.

 

[pbaldy]

Happy to help. I don't know of a cleaner way to get data from a different record in a query.

 

[dsthomson]

http://allenbrowne.com/subquery-01.html#AnotherRecord

Could someone please post an attachment that shows the information in this link applied as a database. I have tried to do this but am unsure as to how to apply it?
Thanks