Advice on Table Structure (1 Viewer)

[matthewnsarah07]

I need some guidance on how I can do the following:

Data will be
[Junction] = text such as "M6 J29 to J30"
[JunctionLength] = number such as '1.88'

Objective
If the data includes numerous junctions with their lengths is it possible to feed in a start and end point and get back the total distance.
Remeniscent of the lookup tables that are in road maps where you match two locations and the result is their distance from each other

Can anyone give me some idea how I could set this up, if its possible

Many Thanks

Matt

 

[Yarp]

Just thinking out loud. I assume distances are the same in both directions.

Fields Value
Motorway M6
J1 J29
J2 J30
Distance 1.88

In this way, you can search for your start junction in J1, then your end junction in J2, and sum the Distance field. I guess if you are travelling in the opposite direction you would just reverse the search on junctions.

Any help??

 

[matthewnsarah07]

Certainly sounds along the right lines

The question comes when there are numerous locations

Say you have J30 to J40 and you have distances between each junctions.

How could I get the table to throw out the total distance between 30 & 40?

 

[Yarp]

Sorry, edit this because it was just plain wrong!!

I was thinking that you would sum the distances on records starting where J1 was 30 and J2 was 40.

So, variables are J1=30 and J2=40. A bit of air code:

SELECT Sum(Distance) FROM Table WHERE J1 BETWEEN [Variable J1] AND [Variable J2-1];

Would mean a slight change to table fields where the junctions are pure numbers (drop the J). Did that make sense?

 

[matthewnsarah07]

I see what yo're getting at

Would this mean that the table would have to include data like

J1 J2
30 31
30 32
30 33

i.e an individual distance for each possible eventuality or could it do a sum of all the distances in between

 

[Yarp]

No, but close

J1 J2
30 31
31 32
32 33

I hope my editted response above makes more sense now.

 

[matthewnsarah07]

I've made a quick test table and it nearly spot on, only one niggle

Data:

Motorway J1 J2 Distance
M6 30 31 2
M6 31 32 1
M6 32 33 3

The SQL Statement works but I noticed if Variable J1 is 30 and Variable J2-1 is 32 the result is given as 6, when it should be 3 in actual fact

Its adding the next distance on

 

[Yarp]

That was my typo there. For [Variable J2-1], try [Variable J2]-1. Does that make sense? If not, post the SQL you used and I will amend it.

 

[matthewnsarah07]

Makes sense now and works exactly right

Really appreciate your assistance with this one - a nice simple solution.

Many Thanks