Solved

# Trip Duration

Posted on 2011-10-05
Medium Priority
288 Views
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
0
Question by:SerinaStar
• 2
• 2

LVL 9

Expert Comment

ID: 36922277
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 Comment

ID: 36922398
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

LVL 9

Expert Comment

ID: 36922412
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

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 36923426
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

Author Closing Comment

ID: 36940702
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
###### Suggested Courses
Course of the Month14 days, 22 hours left to enroll