jwcolby
jwcolby at colbyconsulting.com
Sun Oct 2 13:03:29 CDT 2011
>Do the records in your database have any connection at all to the odometer readings? They would if I were to notice. The thing to remember is that the database is used by potentially many different people. I know where they live because they enter their address into the database. I know the prison address where they pick up the inmates. Everything after that is just "calc distance from A to B". > It's not yet clear to me how to get your addresses out of the database and into the mapping program, but, there's got to be a way. Microsoft's map program has VBA behind it and an API. I will be using that. John W. Colby Colby Consulting On 10/2/2011 12:09 PM, Tina Norris Fields wrote: > Dear John, > > I love it! Do the records in your database have any connection at all to the odometer readings? > (Probably not.) So, you need a way to calculate travel distances between physical addresses. I t > would seem that a mapping program of some sort would provide the solution - just as Google Maps and > MapQuest tell you how far it is from home to the VA hospital in Ann Arbor, for instance. > > It's not yet clear to me how to get your addresses out of the database and into the mapping program, > but, there's got to be a way. Thanks for a great puzzle - don't know how much help I'm going to be > in its solution, but I'm going to give it a solid effort. > > T > > Tina Norris Fields > tinanfields at torchlake.com > 231-322-2787 > > > On 9/28/2011 7:08 AM, jwcolby wrote: >> But... I have database records already in a database. This is not about defining a solution for >> data that does not exist and I would otherwise have to enter. This is about defining a solution >> for data that I (and any other user) must enter into the database to use the inmate pass program. >> >> *It is already in the database* >> >> This is about building a small program to do these calculations about data that has and *is going >> to* accumulate in the course of doing business. >> >> It appears that I am not able to get across to this list the fact that the records already exist >> in a database. >> >> Forget that I am driving. *Just please forget that fact.* I never said anything about actually >> driving these miles, being in a car or anything else related to automobiles. >> >> Now... >> >> I have records in a database which have addresses in them. I need to calculate driving distances >> between those addresses. >> >> Pleeeeeeaaaaaaase, I do not want nor do I need anything related to logging miles driven in a car. >> >> I need a function which takes two addresses and hands back miles between those points, actual >> miles that would be driven. >> >> BUT I AM NOT DRIVING THOSE MILES. The records are somehow just magically (poof) appearing in a table! >> >> John W. Colby >> www.ColbyConsulting.com >> >> On 9/27/2011 11:31 PM, Darryl Collins wrote: >>> Can't add much value to your historical data issue John, but for your future >>> records, well I guess it depends on how much effort and money you want to >>> put into this. >>> >>> There are 'turnkey' solutions already out there for exactly this sort of >>> reporting. These folks will report on miles / kilometres travelled and way >>> much more for about $40 a month. >>> >>> <<http://www.gpsinsight.com/low-cost-gps-tracking>> >>> >>> It actually looks like a bit of over engineering for what you are after, but >>> the concept is exactly what I am talking about. Let the GPS record the >>> mileage for you and just download it to you PC. You might find the service >>> above is worth the $40 per month for your needs. Dunno. Anyway, that seems >>> a lot easier than stuffing around with tracking online maps etc >>> >>> Although the cheapest and best solution I would have thought was the one >>> already suggested. Use a smart phone app, or better still, an old fashion >>> paper log book. >>> >>> Cheers >>> Darryl >>> >>> >>> >>> -----Original Message----- >>> From: accessd-bounces at databaseadvisors.com >>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby >>> Sent: Wednesday, 28 September 2011 12:36 PM >>> To: Access Developers discussion and problem solving >>> Subject: Re: [AccessD] Calculating mileage >>> >>> > I would have thought a standard GPS unit would do this for you pretty >>> much automatically >>> >>> Do what for me? A gps gets you from one place to another. I need code to >>> calculate mileage between >>> two points. >>> >>> I have a database of trips I have taken, hundreds of existing records. I >>> need to calculate the >>> mileage of those trips so that I can apply for a tax refund. >>> >>> Maybe I am missing something here. How is a a gps or a smart phone or a >>> paper log going to help me >>> get mileage calculations for existing and future data records in a database? >>> >>> John W. Colby >>> www.ColbyConsulting.com >>> >>> On 9/27/2011 7:17 PM, Darryl Collins wrote: >>>> I would have thought a standard GPS unit would do this for you pretty much >>>> automatically, although I have bugger all experience in this area. >>>> >>>> >>>> >>>> -----Original Message----- >>>> From: accessd-bounces at databaseadvisors.com >>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby >>>> Sent: Tuesday, 27 September 2011 9:53 PM >>>> 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, >>>>