Calculations using data from more than one record in a table

[Programmer John]

I have a data table with many records each including an event date. I then use queries to extract and sort data that meets certain criteria. My question is how can I calculate the interval between the dates in successive records. What I need is to show the number of days since the previous event for each record extracted by the query. I know the ID numbers of the two records but I can't determine how to jump across record boundaries to make the calculation.

Is this possible?

Thanks.

 

[SydneyGeek]

Yes, but you need a couple of things to make it work.
1. A primary key (Autonumber is much easier than Text)
2. A calculated field in your query that is a query in its own right, pulling the previous value in the current record
3. A third field that identifies the project, if your intervals are based on (say) sales campaigns

Note: You can't use this query for data entry.
Assuming that you have an EventDate field and an EventID primary key, the SQL would be --

SELECT Events.EventID, Events.EventDate, (SELECT T.EventDate FROM Events AS T WHERE T.EventID=(SELECT Max(EventID) FROM Events AS E WHERE E.EventID < Events.EventID)) AS LastEvent, [EventDate]-[LastEvent] AS [Interval]
FROM Events;

Denis

 

[Programmer John]

Thank you very much for the solution. It is a bit more sophisticated than I'm use to but I will certainly try to implement it.