Turn-Around Time

  • Thread starter Thread starter Carol
  • Start date Start date
C

Carol

I need to figure the turn-around time for our operating
rooms. We have 5 operating rooms. I have a "room in"
and "room out" time in my table, so I can easily figure
the total time in each room. But I need to know the time
between cases. So I need to figure the difference between
the out time for one case and the in time for the next
case for each room.

I have racked my brain to try to figure out how to do this
and I'm sure it's not that difficult, but I just have no
clue.

Thanks in advance for your help!!!

Carol
 
well, of course Access doesn't keep records in any particular order, even
though it may look that way when you open a table. so you need a way to find
the value of the RoomOut in the most recent record *before* the record
you're working with, correct? then you can use the value to calculate the
time difference.
suggest you try a DMax() function, something along the lines of

LastOutTime: DMax("RoomOut", "MyTableName", "RoomNumber = " & [RoomNumber] &
" And RoomOut < " & [RoomIn])

substitute the correct table and field names; also, the equations are
written for numeric values. if the bracketed values are text, add single
quotes around them; if they're date/time, add # signs around them.
as a calculated field in your query, this should give you the most recent
"room out" time for each record. you'll have to finish the calculation by
doing the math to compare it to each record's "room in" time, of course.

hth
 
Hi Carol,

Not knowing the structure of your table I'm guessing you have the following:

Record# RoomID RoomIN RoomOut
1 1 8/1/2004 5:30:00 AM 8/3/2004 7:30:00 PM
2 1 8/5/2004 11:30:00 PM 8/7/2004 2:30:00 PM
3 1 8/7/2004 3:30:00 PM 8/10/2004 10:00:00 PM
4 1 8/12/2004 1:00:00 PM 8/13/2004
2:00:00 PM
5 2 7/1/2004 2:00:00 PM
7/3/2004 2:30:00 PM

If that is the case I would add another field called "NextRoomIN". Then
your information should look like

Record# RoomID RoomIN RoomOut
NextRoomIN
1 1 8/1/2004 5:30:00 AM 8/3/2004 7:30:00 PM 8/5/2004
11:30:00 PM
2 1 8/5/2004 11:30:00 PM 8/7/2004 2:30:00 PM 8/7/2004
3:30:00 PM
3 1 8/7/2004 3:30:00 PM 8/10/2004 10:00:00 PM 8/12/2004
1:00:00 PM
4 1 8/12/2004 1:00:00 PM 8/13/2004 2:00:00 PM
5 2 7/1/2004 2:00:00 PM 7/3/2004 2:30:00 PM


To update the above information use VBA code similar to the following:

Function GetRooms()

Dim i As Integer
Dim rsRooms As DAO.Recordset

Set rsRooms = CurrentDb.OpenRecordset("SELECT RoomID,
Min(Table5.RoomIN) AS MinOfRoomIN FROM Table5 GROUP BY RoomID ORDER BY
Min(Table5.RoomIN)")

Do Until rsRooms.EOF
i = UpdateNextRoomIN(rsRooms!RoomID, rsRooms!MinOfRoomIN)
rsRooms.MoveNext
Loop
Set rsRooms = Nothing
End Function

Function UpdateNextRoomIN(RoomID As String, MinRoomIN As Date)

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table5 WHERE RoomID =
'" & RoomID & "' AND RoomIN >#" & MinRoomIN & "# ORDER BY RoomOut")

Dim rs1 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Table5 WHERE RoomID =
'" & RoomID & "' ORDER BY RoomOut")

Do Until rs.EOF
rs1.Edit
rs1!NextRoomIN = rs!RoomIN
rs1.Update
rs1.MoveNext
rs.MoveNext
Loop

Set rs = Nothing
Set rs1 = Nothing

End Function


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights



--------------------
| Content-Class: urn:content-classes:message
| From: "Carol" <[email protected]>
| Sender: "Carol" <[email protected]>
| Subject: Turn-Around Time
| Date: Mon, 16 Aug 2004 08:35:20 -0700
| Lines: 15
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| thread-index: AcSDpqNJ58hdoUNPS+eVfA4EDRLk0g==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.access.queries
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:210179
| NNTP-Posting-Host: tk2msftngxa11.phx.gbl 10.40.1.163
| X-Tomcat-NG: microsoft.public.access.queries
|
| I need to figure the turn-around time for our operating
| rooms. We have 5 operating rooms. I have a "room in"
| and "room out" time in my table, so I can easily figure
| the total time in each room. But I need to know the time
| between cases. So I need to figure the difference between
| the out time for one case and the in time for the next
| case for each room.
|
| I have racked my brain to try to figure out how to do this
| and I'm sure it's not that difficult, but I just have no
| clue.
|
| Thanks in advance for your help!!!
|
| Carol
|
 
This makes absolutely no sense at all. There is no reason whatsoever to add
another time to the table. It just makes one more piece of data that has to
get updated every time a room entry time changes. Get the value from a
query.

Select T.RoomID
, T.RoomOut
, DateDiff("n", T.RoomOut, (SELECT Min(T1.RoomIn) FROM yourTable
T1 WHERE T1.RoomID = T.RoomID AND T1.RoomIn > T.RoomOut)) as RoomTimeBetween
FROM yourTable T
ORDER By T.RoomID, T.RoomOut

HTH
Dale
 
Eric,

Thank you so much for your reply. I have finally had time
to sit down and work on this, and I realized that this
code works with the exception that the date is not taken
into consideration.

Here is the code as I modified it to use the date, also,
but I get and "invalid use of null" error message when I
try to run the code.

I could be WAY off base with this, and could really use
your help again!

Thanks in advance for your help.
Carol

Function GetRooms()

Dim i As Integer
Dim rsRooms As DAO.Recordset

Set rsRooms = CurrentDb.OpenRecordset("SELECT
SurgeryDate, ORSuite, Min(tblORLog.ORTimeIn) AS
MinOfORTimeIn FROM tblORLog GROUP BY SurgeryDate, ORSuite
ORDER BY Min(tblORLog.ORTimeIn)")

Do Until rsRooms.EOF
i = UpdateNextRoomIn(rsRooms!SurgeryDate, rsRooms!
ORSuite, rsRooms!MinOfORTimeIn)
rsRooms.MoveNext
Loop

Set rsRooms = Nothing

End Function

Function UpdateNextRoomIn(SurgeryDate As Date, ORSuite As
Long, MinOfORTimeIn As Date)

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM
tblORLog WHERE ORSuite = ORSuite AND SurgeryDate = # &
SurgeryDate & # AND ORTimeIn >#" & MinOfORTimeIn & "#
ORDER BY ORTimeOut")

Dim rsl As DAO.Recordset
Set rsl = CurrentDb.OpenRecordset("SELECT * FROM
tblORLog WHERE ORSuite = ORSuite AND SurgeryDate = # &
SurgeryDate & # ORDER BY ORTimeOut")

Do Until rs.EOF
rsl.Edit
rsl!NextRoomIn = rs!ORTimeIn
rsl.Update
rsl.MoveNext
rs.MoveNext
Loop

Set rs = Nothing
Set rsl = Nothing

End Function
 
Back
Top