Trip Duration

Posted on 2011-10-05
Last Modified: 2012-05-12

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?

Question by:SerinaStar
    LVL 9

    Expert Comment

    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.

    Author Comment

    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?
    LVL 9

    Expert Comment

    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.
    LVL 92

    Accepted Solution


    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?


    Author Closing Comment

    Thankyou for explaining after reading you advise it all makes sence now...great advise!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now