Trip Duration


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?

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.

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.
SerinaStarAuthor 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?
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.
Patrick MatthewsCommented:

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.


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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SerinaStarAuthor Commented:
Thankyou for explaining after reading you advise it all makes sence now...great advise!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.