By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,584 Members | 3,447 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,584 IT Pros & Developers. It's quick & easy.

Calulate time between different records

P: n/a
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 differencebetween 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
--
Message posted via http://www.accessmonster.com
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Viviana R via AccessMonster.com 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 differencebetween 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

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.
Nov 13 '05 #2

P: n/a
Viviana R via AccessMonster.com 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 differencebetween 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQtcR34echKqOuFEgEQLjuQCgsfWzfJgH0aLH6t34Pt/fPqtuqOsAn1XR
7t3RW4/SDvr6bjDF3of5eRdQ
=dAt/
-----END PGP SIGNATURE-----
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.