Need to Calculate Time Difference Between Two Records

[LearnAsIGo]

I am developing an Access database that is used for timing surgery cases. Most of the calculations needed for the report are from data within a given record, but two of the calculations require times from separate records. I need guidance in developing the following two queries:

1) Difference in time from when one patient is wheeled out of the OR (WheelsOutTime) and the next patient is wheeled into the same OR (WheelsInTime)

2) Difference in time from when the OR was cleaned following one surgery (RmTurnComp) and the time it was set-up for the next surgery (RmSetUpComp).

Following is a sample of the relevant fields form two records:
Date ORRm RmSetUpComp WheelsInTime WheelsOutTime RmTurnComp
1/6/09 OR2 7:15 7:38 13:37 13:48
1/6/09 OR2 14:42 15:04 15:51 16:04

There are multiple OR Rooms so I need to be certain that the criteria used to identify the records used in the calculation narrows the data to the correct OR Room AND that calculations are for surgeries that started on a common date. For example, if the above data were for OR2 and OR3 then the two records would not be valid for the calculations. Also, if the data above were both OR2 but the dates were 1/6/09 and 1/7/09 then once again it would not be valid to use in the calculations.

I hope I have been clear in my explanation and I look forward to assistance from others.

 

[SkipVought]

Hi,

What happens if they are IN on 1/6/2009 @ 23:59 and out the next day?

If it is assumed that they OUT the next day, what if they are really in the OR more than 24 hrs? Maybe that NEVER happens.

The short answer for an OR is
[tt]
TimeIn: (Date+WheelsOutTime)- (Date+WheelsInTime)
[/tt]
BTW, a field named Date is NOT a good idea, as Date is a reserve word in Access, referring to the current date.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}