# Trip Duration

Hi,

I have created a MS Access database which calculates the distance from one point to another. The result is given in Kilometers and Nautical Miles. Now what I need to do now is to calculate the time duration of the trip.

I know I can use the result of the kilometers to calculate the travel time, I just can't think of how and what would be the best way. At the moment I don't have the database recording the results of the distance calculations.

Any suggestions?

Cheers
SS
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I don't know what is the purpose of this database, but you can calculate the time based on average travel speed.

e.g if travel speed is 20kmph then 40km = 2hrs travel time.
0
Author Commented:
The purpose it to calculate total shift times including travel to and from site. Do you know of a way to write that into a query?
0
Commented:
You could create a VBA function in Access to calculate Trip time

e.g. CalculateTripTIme(DistanceTravelled,AvgSpeed)

and make call to that function in query

e.g. Select CalculateTripTime(DistanceTravelled,AvgSpeed)

where DistanceTravelled and AvgSpeed can be Table Fields.
0
Commented:
SerinaStar,

As noted above, if you know the distance traveled and the average speed, then the travel time is simply the distance divided by the speed.

I would recommend that your database store only the distance and the speed, and NOT the travel time.  The reason is that it is usually best not to store derived values: if you store derived values, then you need a way to update the result if any of the inputs change.  You're usually better off leaving out the derived results, and instead calculating them on demand in your queries, forms, and reports.

pritamdutt,

Given that this is simple arithmetic, why go to the trouble of writing a UDF to calculate this?

Patrick
0

Experts Exchange Solution brought to you by