Need help using values from separate records to calculate interval

[Kari in So.Dakota]

I have a database where we are tracking the start and end times of
procedures. We'd like to be able to calcuate an interval from the end of one
procedure to the start of the next. Data is as follows:

ProcedureID Procedure Date StartTime End Time PersonID

We'd like to be able to calculate how long from the end of procedure 1 to
the start of procedure 2 for each person for each date - I've tried a
subquery in my query, but it keeps returning the end time for the first
procedure regardless of PersonID or date. Ultimately we would like to print
a report by person by date and be able to show intervals for each person and
total and average those intervals. Please help. Thanks!

 

[Kari in So.Dakota]

Karl - Thanks so much - that fixed it. The only thing I had to change was in
the second query rather than formatting the difference between the two dates
as hrs, mintes, etc., I used the DateDiff function so that I could add and
average the result in a report based on the query. Very much appreciate your
help!

I put some more sorting in it - try now ---
Kari_1 ---
SELECT Q.ProcedureID, Q.PersonID, Q.[Procedure Date], Q.StartTime, Q.[End
Time], (SELECT COUNT(*) FROM [Kari] Q1
WHERE Q1.[Procedure Date] = Q.[Procedure Date]
AND Q1.[PersonID] = Q.[PersonID]
AND Q1.[StartTime] < Q.[StartTime])+1 AS Rank
FROM Kari AS Q
ORDER BY Q.PersonID, Q.[Procedure Date], Q.StartTime;

Kari_1 --
SELECT Kari_1.ProcedureID, Kari_1.PersonID, Kari_1.[Procedure Date],
Kari_1.StartTime, Kari_1.[End Time],
Format([Kari_1_1].[StartTime]-[Kari_1].[End Time],"h:nn:ss") AS [Time between
procedures]
FROM Kari_1 INNER JOIN Kari_1 AS Kari_1_1 ON (Kari_1.PersonID =
Kari_1_1.PersonID) AND (Kari_1.[Procedure Date] = Kari_1_1.[Procedure Date])
WHERE (((Kari_1_1.Rank)=[Kari_1].[Rank]+1))
ORDER BY Kari_1.PersonID, Kari_1.[Procedure Date];

--
KARL DEWEY
Build a little - Test a little

ProcedID PersonID ProcDate Start End
2 1 3/2/2009 8:01 8:25
1 1 3/1/2009 8:06 8:15
3 2 3/2/2009 8:06 8:35
4 1 3/2/2009 8:30 8:45
5 1 3/2/2009 8:51 9:15

ProcedID is an AutoNumber field, PersonID is a number field ProcDate is
date/time, Start and End are Date/Time formatted as Short Times. Thanks!

Post sample data.
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the info, however, when I try this, it sets all the Rank fields to
2, regardless of the number of records for each PersonID - any further
thoughts?

:

Try these queries --
Kari_1
SELECT Q.ProcedureID, Q.PersonID, Q.[Procedure Date], Q.StartTime, Q.[End
Time], (SELECT COUNT(*) FROM [Kari] Q1
WHERE Q1.[Procedure Date] = Q.[Procedure Date]
AND Q1.[PersonID] = Q.[PersonID]
AND Q1.[StartTime] < Q.[StartTime])+1 AS Rank
FROM Kari AS Q
ORDER BY Q.StartTime;

Kari_2
SELECT Kari_1.ProcedureID, Kari_1.PersonID, Kari_1.[Procedure Date],
Kari_1.StartTime, Kari_1.[End Time],
Format([Kari_1_1].[StartTime]-[Kari_1].[End Time],"h:nn:ss") AS [Time between
procedures]
FROM Kari_1 INNER JOIN Kari_1 AS Kari_1_1 ON (Kari_1.[Procedure Date] =
Kari_1_1.[Procedure Date]) AND (Kari_1.PersonID = Kari_1_1.PersonID)
WHERE (((Kari_1_1.Rank)=[Kari_1].[Rank]+1));

--
KARL DEWEY
Build a little - Test a little


:

I have a database where we are tracking the start and end times of
procedures. We'd like to be able to calcuate an interval from the end of one
procedure to the start of the next. Data is as follows:

ProcedureID Procedure Date StartTime End Time PersonID

We'd like to be able to calculate how long from the end of procedure 1 to
the start of procedure 2 for each person for each date - I've tried a
subquery in my query, but it keeps returning the end time for the first
procedure regardless of PersonID or date. Ultimately we would like to print
a report by person by date and be able to show intervals for each person and
total and average those intervals. Please help. Thanks!