Crosstab and Calc Time Difference

[ColeChaW]

Hello again everyone!

My head's swimming right now so the solution to this problem may be right under my nose and I'm just blind to it.

I have a table with events, represented by an ID number, and the date/time the events occured. Each event is also attatched to a particular mission number. Looks kinda like this:

Mission EventID Time

Each Mission has different events like liftoff and patient contact, and I need to find the time between events for each mission. I'm trying to calculate the Time from inital call to mission end, from dispatch to liftoff, ect.

Do I need to put these times into my mission table as columns, Time1, Time2, Time3... or can I leave them normalized and still run calculations on them?

 

[ColeChaW]

Table structure

tblMission
MissionID (autonumber)
...otherstuff...

tblEvent
EventID (autonumber)
EventName (text)
EventDescription (text)

tblEventDateTime
MissionID (number)
EventID (number)
DateTime (datetime)