Distance Calculation

[Lee Chai Ling]

Dear All:

I have a problem in calculating distance's mileage.Eg: Distance from town A to town B is 10km, town A to town C is 20km and vice versa. I have a table named "Location" as per below:-

Town From , Town To , Distance
A , B , 10km
A , C , 20km
A , D , 30km
B , A , 10km
C , A , 20km
D , A , 30km

My question now is: if there are 26 locations, (Eg: A - Z), then I need to key in the distance one by one and it is very tedious. Because Town A to Town B is 10km and Town B to Town A also 10km.

Is there any method that Access will consider A to B and B to A is same distance?

Can we use crosstab to calculate?? and what is the step?

Pls help & thanks in advanced.

 

[Jon K]

You can enter the distance between each pair of towns once:

TownFrom	TownTo	Distance
A		B	10km
A		C	20km
A		D	30km
A		E	40km
............................
............................
B		C	50km
B		D	60km
B		E	70km
............................
............................
C		D	80km
C		E	90km
............................
............................

and then complete the other half of the table with an Append query:

INSERT INTO [TableName] ( TownTo, TownFrom, Distance )
SELECT TownFrom, TownTo, Distance
FROM [TableName];
.

 

[Lee Chai Ling]

Jon K

Thanks!!!