Calulate time between different records

**Salad** · Nov 13 '05, 01:02 PM

Re: Calulate time between different records

Viviana R via AccessMonster.c om wrote:
[color=blue]
> I'm tryin to calculate de difference of time between different records and
> fields.
>
> I have a Report with date, time In, and Time Out fields.
> EX:
> Date Time IN Time Out
> 4/12/05 12:10 PM 12:40 PM
> 4/12/05 12:50 PM 1:20 PM
> 4/12/05 1:15 PM 1:45 PM
>
> 4/13/05 9:00 AM 9:35 AM
> 4/13/05 10:00 AM 10:20AM
>
> I need to calculate the difference between Time In and Time Out. The
> difference between the first time Out and the second Time IN (4/12/05 12:40
> PM and 4/12/05 12:50 PM) then the second Time Out and third Time In(4/12/05
> 1:20 PM and 4/12/05 1:15 PM). Also the differencebetwe en First Time In and
> Second Time In ...(4/12/05 12:10 PM and 4/12/05 12:50 PM)
>
> If anybody have ideas how to aproach this i would appreciate it.
> Thanks, Viviana
>
>[/color]
Check out DateDiff. You can get the minutes, seconds, hours between two
times.

Regarding the differences between the first time in and the next time
in, I'd probably cheat, break rules, and I really wouldn't care if some
programmer came behind and snorted and said it wasn't following some
relational rules. I would create a new field called PriorTimeIn. When
I add a record, I would check for the max date/time for that day and
store that time into that field. Then I'd have one record with the
prior time in so I could calc the time diffs for timein's and I could
calc the diff between timein/out.

**MGFoster** · Nov 13 '05, 01:03 PM

Re: Calulate time between different records

Viviana R via AccessMonster.c om wrote:[color=blue]
> I'm tryin to calculate de difference of time between different records and
> fields.
>
> I have a Report with date, time In, and Time Out fields.
> EX:
> Date Time IN Time Out
> 4/12/05 12:10 PM 12:40 PM
> 4/12/05 12:50 PM 1:20 PM
> 4/12/05 1:15 PM 1:45 PM
>
> 4/13/05 9:00 AM 9:35 AM
> 4/13/05 10:00 AM 10:20AM
>
> I need to calculate the difference between Time In and Time Out. The
> difference between the first time Out and the second Time IN (4/12/05 12:40
> PM and 4/12/05 12:50 PM) then the second Time Out and third Time In(4/12/05
> 1:20 PM and 4/12/05 1:15 PM). Also the differencebetwe en First Time In and
> Second Time In ...(4/12/05 12:10 PM and 4/12/05 12:50 PM)
>
> If anybody have ideas how to aproach this i would appreciate it.
> Thanks, Viviana[/color]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Viv, The solution could be like a running-sum query.

You can do this in the query that feeds the report (the Report's
RecordSource). I don't know what your DB schema looks like, but, if all
the data is coming from one table, the solution would look something
like this:

SELECT DISTINCT C.work_date, C.time_in, C.time_out,
DateDiff("n", P.work_date + P.time_out, C.work_date + C.time_in) As
down_time,
DateDiff("n",P. work_date + P.time_in, C.work_date + C.time_in) As
interval_time

FROM TimeSheet As C LEFT JOIN TimeSheet As P
ON C.work_date >= P.work_date AND C.time_in > P.time_out

ORDER BY C.work_date, C.time_in

WHERE < your criteria >

The C table is the current work_date table and the P table is the
previous work_date table.

The first DateDiff() calculates the minutes between the previous
record's time_out and the current record's time_in. The 2nd DateDiff()
calculates the minutes between the current record's time_in and the
previous record's time_in.

Becaue the JOIN's ON clause will cause a NULL record when the time
changes from PM to AM over a day boundary, the down_time & interval_time
will be NULL. Hopefully, you don't want to count the minutes between
end of day and beginning of next day, 'cuz I can't figure out how to get
that, yet.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtcR34echKq OuFEgEQLjuQCgsf WzfJgH0aLH6t34P t/fPqtuqOsAn1XR
7t3RW4/SDvr6bjDF3of5eR dQ
=dAt/
-----END PGP SIGNATURE-----

Calulate time between different records

Calulate time between different records

Comment

Comment