Viviana R via AccessMonster.c om wrote:
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
-----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-----