Code for getting data from last into the next record in a query

[MikeSawt]

I am using Access 2000

I am trying to calculate a turnover time between surgeries in an operating room suite.

I have multiple Operating Room.

I am running a query which will sort by the date and by room number.

The Table Name is: SurgLog
The two field names that I am interested in.
[TimeToRoom]
[TimeFromRoom]

I have a third field called [LastSurgEndingTime] that will equal the previous [TimeFromRoom]

A fourth field is [DeltaFromTo]
DeltaFromTo: [LastSurgEndingTime]-[TimeToRoom]
This is the turnover time.

What is the correct code to have next [LastSurgEndingTime] equal to the last records [TimeFromRoom]?

This is an easy equation in Excel but I cannot find the correct syntax for Access.

Thanks for the help

Mike

 

[Crowley16]

I don't think you can do this easily by using queries only...

I suppose you could try to make 2 queries and add a number index based on the order of records returned, and then join these 2 queries up with the index offsett by 1...

--------------------
Procrastinate Now!

 

[BSman]

You could use the DMax function something like this:

Build a query that basically selects all of the columns in SurgLog. Let's call it qselSurgLog.

Then build a second query on qselSurgLog with an added column LastSurgEndingTime as follows:

LastSurgEndingTime: DMax("[TimeFromRoom]","[SurgLog]","[TimeFromRoom] < #" & [TimeToRoom] & "#")

Use the build function in the query builder matrix to get the exact syntax, but the important thing is that TimeToRoom is from qselSurgLog. The basic ideas is that you want the most recent exit date/time in the SurgLog table that is before the current record's surgery starting time (TimeToRoom).

This may not run as fast as you would like, but should provide the data you want.

 

[MichaelRed]

This topic has been oft and repeatedly discussed in these fora. In particular, Member PHV has recently posted a number of SQL ONLY soloutions to some of these inquiries.

I can post a sample table and query, along w/ the results, adapted from one recent thread:

The Table

dtRead	MtrRead
1/1/2004	127385
1/8/2004	129338
1/15/2004	131644
1/22/2004	134702
1/29/2004	136922
2/5/2004	139090
2/12/2004	142156
2/19/2004	146221
2/26/2004	149112
3/6/2004	152135
3/15/2004	156400
3/22/2004	158221
3/29/2004	161221
4/6/2004	163451
4/13/2004	166212
4/20/2004	168314
4/27/2004	171169
5/4/2004	173468
5/11/2004	175844
5/19/2004	178223
5/26/2004	181300
6/1/2004	183444

The (SQL) Query

SELECT A.dtRead, A.MtrRead, [A].[MtrRead]-(SELECT B.MtrRead FROM tblRec2Rec as B WHERE B.dtRead = (SELECT Max(C.dtRead) FROM tblRec2Rec C WHERE C.dtRead < A.dtRead)) AS MtrDiff, [A].[dtRead]-(SELECT B.dtRead FROM tblRec2Rec as B WHERE B.dtRead = (SELECT Max(C.dtRead) FROM tblRec2Rec C WHERE C.dtRead < A.dtRead)) AS DaysDiff, [MtrDiff]/[DaysDiff] AS DailyUse
FROM tblRec2Rec AS A;

The results

dtRead	MtrRead	MtrDiff	DaysDiff	DailyUse
1/1/2004	127385			
1/8/2004	129338	1953	7	279.00
1/15/2004	131644	2306	7	329.43
1/22/2004	134702	3058	7	436.86
1/29/2004	136922	2220	7	317.14
2/5/2004	139090	2168	7	309.71
2/12/2004	142156	3066	7	438.00
2/19/2004	146221	4065	7	580.71
2/26/2004	149112	2891	7	413.00
3/6/2004	152135	3023	9	335.89
3/15/2004	156400	4265	9	473.89
3/22/2004	158221	1821	7	260.14
3/29/2004	161221	3000	7	428.57
4/6/2004	163451	2230	8	278.75
4/13/2004	166212	2761	7	394.43
4/20/2004	168314	2102	7	300.29
4/27/2004	171169	2855	7	407.86
5/4/2004	173468	2299	7	328.43
5/11/2004	175844	2376	7	339.43
5/19/2004	178223	2379	8	297.38
5/26/2004	181300	3077	7	439.57
6/1/2004	183444	2144	6	357.33
[/b]

Of course, the credit for the soloution should be attribuited to [b][COLOR=blue]PHV[/color][/b], as my effort was only to suggest minor modification which extended the soloution beyond the original request.

If you can at least murk your way through the SQL, you can make a 'clone' of the above and nodify it to suit your table/field names.





MichaelRed