turnaround time for different records and different fields (1 Viewer)

[dave_UFgator]

i'm relatively advanced Access user but this problem has got me stumped....i've been given a table that's basically a log of operating rooms with fields designating the date, the room number, "patient in room" time and "patient out of room" time. basically, i want to calculate the turnaround time which is the "patient in room" time" minus the "patient out of room" time from the previous record (if records are sorted in sequential order).

any thoughts?

 

[KeithG]

DateDiff('h',[TimeIn],[TimeOut])

the above will calculate the number of hours between the two times.

 

[KeithG]

Actually that will only work if the in and out time are in the same record. Can you post an example db? Do you have an autonumber for you PK? I have some logic in a program I have here at work to find the next recorded entered by a user an calculate the time between the two records. Is this what you are wanting to do?

 

[dave_UFgator]

thanks for the help. here's a sample of the database:

ID ProcDate LabNumber pt_inroom pt_outroom
2209 7/3/2006 1 7:26 8:23
2210 7/3/2006 1 8:32 9:44
2211 7/3/2006 1 9:40 10:47
2212 7/3/2006 1 11:15 11:54
2213 7/3/2006 1 13:27 14:53


so the turnaround time for record with id#2210 would be 9 minutes.

 

[dave_UFgator]

yes, that sounds exactly like what i'm trying to do. here's a sample of my database:

ID ProcDate LabNumber pt_inroom pt_outroom
2209 7/3/2006 1 7:26 8:23
2210 7/3/2006 1 8:32 9:44
2211 7/3/2006 1 9:40 10:47
2212 7/3/2006 1 11:15 11:54
2213 7/3/2006 1 13:27 14:53

so the turnaround time for record #2210 would be 9 minutes (time in minus time out for previous record).

 

[KeithG]

I dont understand? How did you figure 9 minutes?

 

[KeithG]

Try the below SQL. Change Sheet1 to your table name.


SELECT Sheet1.ID, Sheet1.ProcDate, Sheet1.LabNumber, Sheet1.pt_inroom, DMin("pt_inroom","sheet1","ID=" & (DMin("ID","Sheet1","ID>" & [ID]))) AS NextIn, DateDiff('n',[pt_inroom],[NextIn]) AS Expr1
FROM Sheet1;

 

[dave_UFgator]

patient left room #1 in record #2209 at 8:23. in the same room, the next patient comes in at 8:32 (record #2210). so it's not only different records but different fields (patient in field minus patient out field).

does that help?

 

[KeithG]

The last SQL I posted is not what you want so try the below. Replace Sheet1 with your table name.


SELECT Sheet1.ID, Sheet1.ProcDate, Sheet1.LabNumber, Sheet1.pt_inroom, DLookUp("pt_outroom","sheet1","ID=" & (DMax("ID","Sheet1","ID<" & [ID]))) AS Previous, DateDiff('n',[Previous],[pt_inroom]) AS Expr1
FROM Sheet1;

 

[dave_UFgator]

keith, i'm getting a #error in the query. here's what i used:

SELECT table1.ID, table1.ProcDate, table1.LabNumber, table1.[pt in room], DLookUp("pt out room","table1","ID=" & (DMax("ID","table1","ID<" & [ID]))) AS Previous, DateDiff('n',[Previous],[pt in room]) AS turn
FROM table1;

is the syntax right on the functions?

 

[dave_UFgator]

keith, never mind my last post. i figured out the small syntax problem. works great. thanks!!!