Subtract a field from a previous record from the current record

R

RCM

I have two fields in each record, Start Time and Stop Time. I want to
calculate the time between the Start Time of the current record and the Stop
Time of the previous record. I am using a query to gather the raw data. How
do I do this?
 
M

[MVP] S.Clark

The problem lies in the fact that makes a record the 'Next' or the
'Previous'? How is this determined? Is there an autonumber that defines
each record or a timestamp when the record was created? Once you know this,
then you can write the query to perform the 'find'(which may take one or two
queries) and then also the calculation.(which may be done in one of the
previous, or maybe a new query). Also, you can probably do some fun tricks
with a subquery.

List your table structure, and maybe someone can help.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting
 
J

JohnFol

Is the result going to be displayed on a form / report, or exported
somewhere else?
 
P

peregenem

[MVP] S.Clark said:
The problem lies in the fact that makes a record the 'Next' or the
'Previous'?
Click to expand...

I'm guessing the OP has non-overlapping intervals, thus the DATETIME
values themselves dictate the order e.g.

CREATE TABLE Test (
start_time DATETIME NOT NULL,
stop_time DATETIME)
;
INSERT INTO Test VALUES
(#2005-08-01 09:00:00#, #2005-08-01 12:00:00#)
;
INSERT INTO Test VALUES
(#2005-07-31 09:00:00#, #2005-07-31 17:00:00#)
;
INSERT INTO Test VALUES
(#2005-07-30 11:00:00#, #2005-07-30 15:00:00#)
;
INSERT INTO Test VALUES
(#2005-07-29 08:00:00#, #2005-07-30 03:00:00#)
;

SELECT T1.start_time,
(SELECT MAX(T2.stop_time)
FROM Test AS T2
WHERE T2.stop_time < T1.start_time
) AS previous_stop_time,
DATEDIFF('n', previous_stop_time, T1.start_time)
AS difference_minutes
FROM Test AS T1;
 
R

RCM

I am using indexing on the date field then the stop time field. The
following is the field expression I used. It current does not pull a value
at all. My goal is determine how long it takes to start the next process
within the same day.

PrevTime_out: (select max(TIME_OUT01) from TurnOver where ADMDT =
TurnOver.ADMDT and ORTIMEIN01 >= TurnOver.TIME_OUT01)
 
R

RCM

The problem there is that there are 4 rooms in operation. Each has its own
times, so they can overlap. The operator drives the calculation.
 
M

[MVP] S.Clark

So, find the max time per RoomID. Then find the max time per RoomID that is
less than the first found max time. That gives you the Current and Previous
Max times, of which you can use to perform a subtraction to get the elapsed
time.

Use DateDiff() if you like.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting
 
Top