Calculating distances
With the help of DACM I suggest the following solution, which includes a README form.Antonic wrote:This is my issue.
I have a table with for instance 5 cities:
City1
City2
City3
City4
City5.
I know the distances between all of them. How can I sum up the distances by only naming the cities i am going through.
City1-City2=20km
City2-City3=30km
City3-City4=15km
City4-City5=25km.
And now i say i am going from city1 through city2 to city3. I need the road length.
Thanks
As always, it may a little bit ugly for I was focussed on getting the queries right and it is free of any macro poison.
The queries try to resemble a simple step-by-step approach where each query depends on a preceeding one.
Fill in your own data:
All you've got to do now is to enter your list of location names in table "Cities", run the following INSERT (Tools>SQL...) statement and fill out the distances between the entered cities (form "Enter Distances").
Code: Select all
INSERT INTO "DIST"
(
SELECT C1.ID AS CID1, C2.ID AS CID2, NULL
FROM "Cities" AS C1, "Cities" AS C2
WHERE CID1<CID2
AND NOT EXISTS
(SELECT * FROM "DIST" WHERE C1.ID || C2.ID = CID1 || CID2)
);
The subforms on the right show the saved form's collected data in right order and the aggregated distances (sum, count, average, ...).
This is how to delete all test data in the right order:
menu:Tools>SQL...
Code: Select all
DELETE FROM "T_C";
DELETE FROM "DIST";
DELETE FROM "Cities";
DELETE FROM "Tours";