Calculating Mileage (1 Viewer)

Fozi

Registered User.
Local time
Today, 21:11
Joined
Feb 28, 2006
Messages
137
:confused:

Folks

I'm working on a Travel and Expenses system and trying to come up with a solution to the following. Company has three bases. Company policy dictates that if departing from home to work at a location not your base, then you should clain the smallest distance between either Home - Work or Base to Work. So for example:

Employee A
Lives 6 miles from Office A. He is based at Office B.
Distance between Office A to Office B is 10 miles.

He travels from Home to Office A and therefore should claim 6 miles but claims 10.

Employee B:
Lives 12 miles from Office A. She is based at Office B.
Distance between Office A to Office B is 10 miles.

She travels from Home to Office A and therefore should claim 10 miles but claims 12.

I know it's a relatively simple if statement but I cannot figure out how or where to integrate it. I have a Staff Table and a Claim Table. Happy to attached if necessary.

Thanks for any assistance provided.

Frank
 

CBrighton

Surfing while working...
Local time
Today, 21:11
Joined
Nov 9, 2010
Messages
1,012
Depends where all the distances are stored.

I would guess that distances from home to each office is stored in the staff table (or another table linked to the staff table as one to many) but where would the distances between offices be stored?

Attaching the database may be easiest. 2003 format would be best, otherwise I can't open it. :)
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:11
Joined
Sep 12, 2006
Messages
13,892
dbs way

you would need to store

a) an officelink table, storing all inks between offices, and the distances (therefore n * n+1 /2 entries - where n is no of offices)

b) an employee distances table, storing home to work distances for each employee to each office

---------
the other way is maybe to have something like mappoint - store the lat/longs of each office/employee, and get mappoint to work out road distances from the lat longs as they are needed,

or even use mappoint to populate the data in the first idea.

-----------
I use mappoint. you may be able to get the data from google maps, but i have no experience with this. Its hard in the UK, as google doesnt have (ie isnt allowed to use) sufficienly accurate post code data.
 

CBrighton

Surfing while working...
Local time
Today, 21:11
Joined
Nov 9, 2010
Messages
1,012
I can confirm that Google Maps is fairly simple to automate via VBA, but I don't know about the accuracy.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom