Event Log strategy

[kman42]

I am creating an event log and I need to calculate a duration by comparing the date of the event to the date of the next event. What is the best strategy for doing this?

Here is my design:

tbl_Project
pk_ProjectID

tbl_Events
pk_EventID
EventName
EventDescription

tbl_EventLog


pk_EventLogID
fk_ProjectID
fk_EventID
DateOfEvent
DurationOfEvent

I'm not sure of the best strategy to calculate DurationOfEvent. Conceptually, it is just the time elapsed until the date of the next event or the current date if it is the last event for that project. How would I create a calculated field that would find all the events for a project, then subtract the date of the current event from the date of the next event?

Thanks,
Kman

[orange]

One strategy is to use a subquery. see http://allenbrowne.com/subquery-01.html