Calculate the travel time hours

[abu_saleh]

Hello everyone:

I have a 97 MS Access issue, I need to get resolve. A salesperson goes one
store and spent few minutes for try to sale products and then leave the store
and then start drive for the next store. After reach to the next store,
salesperson goes inside the store and spend few more minutes and then come
out and go for the next store and so on. Only field the salesperson fill up
is the store TimeIn and TimeOut fields. At the end of the day, what will be
the total store spent hour and the total travel hour for this sales person. I
already have the formula for the total store spent hours.

How do I get the travel time hours since the travel time determined from the
start from the first record TimeOut field and end at the next record (2nd
record) TimeIn field. Below is a sample example.

TimeIn and TimeOut fields are Date/Time data type for the data table.


Days TimeIn TimeOut StoreTime/Min
TravelTime/Min
FR 8:10:00 AM 9:00:00 AM 0.50
FR 10:00:00 AM 10:35:00 AM 0.35
FR 11:05:00 AM 11:30:00 AM 0.25
FR 12:00:00 PM 12:17:00 PM 0.17
FR 12:35:00 PM 12:49:00 PM 0.14
FR 1:05:00 PM 1:27:00 PM 0.22
FR 1:40:00 PM 2:00:00 PM 0.20
FR 3:03:00 PM 3:27:00 PM 0.24
FR 3:40:00 PM 4:05:00 PM 0.25

How do I calculate the salesperson total travel time hours for that day?

If anyone can help me as soon as possible that would be great. You can email
me at (e-mail address removed)

Thank you very much in advance.

Abu Saleh

 

[Duane Hookom]

If this is to be totalled in a report, you can use an expression like:
=Max(TimeOut)-Min(TimeIn) - Sum(TimeOut - TimeIn)

 

[Marshall Barton]

I have a 97 MS Access issue, I need to get resolve. A salesperson goes one
store and spent few minutes for try to sale products and then leave the store
and then start drive for the next store. After reach to the next store,
salesperson goes inside the store and spend few more minutes and then come
out and go for the next store and so on. Only field the salesperson fill up
is the store TimeIn and TimeOut fields. At the end of the day, what will be
the total store spent hour and the total travel hour for this sales person. I
already have the formula for the total store spent hours.

How do I get the travel time hours since the travel time determined from the
start from the first record TimeOut field and end at the next record (2nd
record) TimeIn field. Below is a sample example.

TimeIn and TimeOut fields are Date/Time data type for the data table.


Days TimeIn TimeOut StoreTime/Min
TravelTime/Min
FR 8:10:00 AM 9:00:00 AM 0.50
FR 10:00:00 AM 10:35:00 AM 0.35
FR 11:05:00 AM 11:30:00 AM 0.25
FR 12:00:00 PM 12:17:00 PM 0.17
FR 12:35:00 PM 12:49:00 PM 0.14
FR 1:05:00 PM 1:27:00 PM 0.22
FR 1:40:00 PM 2:00:00 PM 0.20
FR 3:03:00 PM 3:27:00 PM 0.24
FR 3:40:00 PM 4:05:00 PM 0.25

How do I calculate the salesperson total travel time hours for that day?

If anyone can help me as soon as possible that would be great. You can email
me at (e-mail address removed)

SELECT Sum(StoreTime) As TotalStoreTime,
DateDiff("n", Min(TimeIn), Max(TimeOut)) - Sum(StoreTime)
FROM table
WHERE Days = "FR"

 

[abu_saleh via AccessMonster.com]

Thank you very much Duane for your reply. This is great. This give me an idea
how I can get the travel time out of this. Thanks again. Really appreciated.

 

[abu_saleh via AccessMonster.com]

Thank you very much Marshall for your reply. This is great. Same as Duane
replay, this give me an idea how I can get the travel time out of this.
Thanks again. Really appreciated.