[AccessD] Calculating mileage

I would contact a company like Transfinder.com and ask them how they do it.
They plan bus routes. I know they use a lot of stuff besides shortest
possible route, but I am sure that is their starting point.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Wednesday, September 28, 2011 8:39 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Calculating mileage

John,

 Check out the following two links:

http://rdsrc.us/7mwAve

 This has a solution from Helen Feddema, which places a Google Map in a web
browser control and allow you to get the route and mileage between zips.
This is not exactly what you want, but it shows you the basic technique of
formatting a URL for Google Maps.

http://rdsrc.us/XqHekz

 This second one is a lot closer to what you want, but it was done in an
Excel spreadsheet.  Still VBA code though and you should be able to drop it
into Access except for the cell references.  Scroll to the bottom and about
four comments back.  Look for the one from Patrick with the download
web-Google-maps-distances-10-2-.xls.

  He notes that this is for distance between zips, but that an address in
the to/from column would work as well, so that should suite your purposes.

  There may be a solution that's dead on, but I don't have the time to
search at the moment.  Between the two above, you should be able to figure
it out fast enough.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, September 27, 2011 07:53 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Calculating mileage

Before tax time I need to add a module to my inmate checkout to calculate
mileage for all legs of a 
trip with the inmates.  The database contains records of where we go - the
midpoints points - with 
addresses, but the database does not have my start / end point (home).

1) Home to prison
2) Prison to first destination in the db
3) First destination to second destination (if any)
4) Second destination to third destination (if any)
5) Last destination to prison
6) Prison to home

I thought I would try to use Microsoft Streets and trips to do the mapping,
though I am open to 
using Google Maps if there is a programming interface available to the VBA
language.

I could of course manually enter the trips into Streets and trips, get the
mileage and build a table 
of all the "legs" that I have ever done but that is error prone and forces
me to do the whole thing 
manually.  Also this is in use by other people so it would be nice to build
a module that would also 
calculate their legs, which I certainly do not want to do myself manually.

If for tax purposes I just assume that we always start from home and end at
home I could use 
something like Streets and trips (which I have) to calculate all of these
legs for each trip and 
place them in a table.  At tax time I then have a mileage log for the IRS.

I have never used Streets and Trips for this purpose.  I do know that it has
an API and VBA 
available for it.  If anyone uses it for this purpose and has a library (or
a function) written to 
do this that they would share that would be great.  Or a web or help
resource for this.

Thanks,

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com