MS Access -All the possible routes one can travel through to get from A to E

[dvdadler via AccessMonster.com]

I am trying to come up with the VBA code I can use in a small application
I've inherited to do the following:

The access database list a number of cities and the length of the roads that
link them

Each city is not necessarily directly connected to each other (see example
below)

What I am trying to do is come up with a list of all the rotes which link a
given point to another (in this case point A to point E)

The sample database has only a handful of cities/ roads but the real listing
may contain a 200 cities and 130 roads

Can any of you provide me so guidance as to how one tackle such a challenge
(I am sorry to say it but I am relatively new to VBA / Access programming)?

I hope that my little brainteaser doesn't turn out to be as difficult as the
infamous Travelling Salesmen Problem (TSP)

Thanks for your help

David

 

[dvdadler via AccessMonster.com]

Oops

I am new to this thing so in my excitement I forgot to add the following:

List of all the towns and routes bw them

From To Route# Description Distance
A B 10 A TO B 10
A D 60 A TO D 5
B E 40 B TO E 8
B C 20 B TO C 11
C E 50 C TO E 5
C A 30 C TO A 3
E D 70 E TO D 6


I

 

[Marshall Barton]

List of all the towns and routes bw them

From To Route# Description Distance
A B 10 A TO B 10
A D 60 A TO D 5
B E 40 B TO E 8
B C 20 B TO C 11
C E 50 C TO E 5
C A 30 C TO A 3
E D 70 E TO D 6

Not a "little" brain teaser, but not the traveling salesman
either. It will take some serious homework, but study up on
navigating the nodes of a graph (and avoiding going in
circles). In this case a "graph" is a mathematical term
that many algorithms have been created to deal with
questions such as yours. For example, you could (and I am
not suggesting you should) use boolean logic in a linear
algebra that raises a triangular matrix to the Nth power to
find the cities that are connected via N trips between
cities (loops through the same city not excluded).

 

[David C. Holley]

A few years back, I played around with the idea of automating Mappoint
http://www.microsoft.com/mappoint/en-us/default.aspx using addresses from
within an Access database. The project got nixed, however it did look
somewhat promising. Since Mappoint *IS* a mapping application, it might be
wise to let MP do all of the hard word. The automation, like anything else,
is just a matter of learning the Mappoint Object Model. Its been at least 8
years (YIKES!) but from what I recall Mappoint allows routes to be saved and
then recalled.

 

[David C. Holley]

Given that SQL Server Express is a free download, this add-in looks rather
interesting http://msdn.microsoft.com/en-us/library/ee310126.aspx

 

[dvdadler via AccessMonster.com]

A few years back, I played around with the idea of automating Mappoint
http://www.microsoft.com/mappoint/en-us/default.aspx using addresses from
within an Access database. The project got nixed, however it did look
somewhat promising. Since Mappoint *IS* a mapping application, it might be
wise to let MP do all of the hard word. The automation, like anything else,
is just a matter of learning the Mappoint Object Model. Its been at least 8
years (YIKES!) but from what I recall Mappoint allows routes to be saved and
then recalled.
[quoted text clipped - 45 lines]

find the cities that are connected via N trips between
cities (loops through the same city not excluded).

Thanks for your comments

I will have a look at Mappoint (it is always good idea to learn new tricks
but alas whee does one find the time to do so) but I am still keen to see if
I can find a simple solution to append to the existing Access application I
inherited.

I'll keep you posted if and when I make some progress

P.S I can appreciate the sentiment behind the "Yikes" statement. It was only
yesterday when I was a crack (well at least in comparison to my lesser
experienced mates) Fortran programmer (that will give you some idea as to my
age group) and here I am struggle to get my head around the MS Access and VBA

 

[dvdadler via AccessMonster.com]

Given that SQL Server Express is a free download, this add-in looks rather
interesting http://msdn.microsoft.com/en-us/library/ee310126.aspx
[quoted text clipped - 45 lines]

find the cities that are connected via N trips between
cities (loops through the same city not excluded).

interesting!

Thanks once again

 

[dvdadler via AccessMonster.com]

Marshall

Thanks for your advice
It is my first ever attempt to solicit help from the general public (on this
or any other site) and I was most excited to get such a prompt response

At this stage I am fighting on at least two fronts (the challenge of the
given problem and my lack of MS Access/ VBA – I am a graduate of Google 101)
but I am sure an answer would be found (it may be ugly but it would work)

I spent a couple of hrs looking at the problem and I can see a mathematical
pattern which hopefully I would be able to translate into a working
model/program

Someone also suggested looking into recursive tree solutions something which
I would investigate as soon as I can (Google here I come!)

I'll keep you posted and I would appreciate any other advice

David

List of all the towns and routes bw them

[quoted text clipped - 27 lines]
Not a "little" brain teaser, but not the traveling salesman
either. It will take some serious homework, but study up on
navigating the nodes of a graph (and avoiding going in
circles). In this case a "graph" is a mathematical term
that many algorithms have been created to deal with
questions such as yours. For example, you could (and I am
not suggesting you should) use boolean logic in a linear
algebra that raises a triangular matrix to the Nth power to
find the cities that are connected via N trips between
cities (loops through the same city not excluded).

 

[Marshall Barton]

I think Vanderghast replied to your question in the previous
thread so you might want to look there. Basically, he
suggested that you look at:
http://en.wikipedia.org/wiki/Dijkstra's_algorithm

 

[David C. Holley]

And hopefully you're just dealing with the shortest physical route and not
the most efficient which would have to factor in issues such as speed
limits, delivery schedules, and trailer load order.

Marshall

Thanks for your advice
It is my first ever attempt to solicit help from the general public (on
this
or any other site) and I was most excited to get such a prompt response

At this stage I am fighting on at least two fronts (the challenge of the
given problem and my lack of MS Access/ VBA - I am a graduate of Google
101)
but I am sure an answer would be found (it may be ugly but it would work)

I spent a couple of hrs looking at the problem and I can see a
mathematical
pattern which hopefully I would be able to translate into a working
model/program

Someone also suggested looking into recursive tree solutions something
which
I would investigate as soon as I can (Google here I come!)

I'll keep you posted and I would appreciate any other advice

David

List of all the towns and routes bw them

[quoted text clipped - 27 lines]

I hope that my little brainteaser doesn't turn out to be as difficult as
the
infamous Travelling Salesmen Problem (TSP)

Not a "little" brain teaser, but not the traveling salesman
either. It will take some serious homework, but study up on
navigating the nodes of a graph (and avoiding going in
circles). In this case a "graph" is a mathematical term
that many algorithms have been created to deal with
questions such as yours. For example, you could (and I am
not suggesting you should) use boolean logic in a linear
algebra that raises a triangular matrix to the Nth power to
find the cities that are connected via N trips between
cities (loops through the same city not excluded).

 

[dvdadler via AccessMonster.com]

Marshall

My need is even simpler for i do not care which one is the shortest. All i am
after is a list of all the routes and related data (I am sure once I get that
I will be able to use it for any other analysis e.g comparative distance bw
the routes etc

David

And hopefully you're just dealing with the shortest physical route and not
the most efficient which would have to factor in issues such as speed
limits, delivery schedules, and trailer load order.

Marshall

[quoted text clipped - 38 lines]

 

[dvdadler via AccessMonster.com]

I think Vanderghast replied to your question in the previous
thread so you might want to look there. Basically, he
suggested that you look at:
http://en.wikipedia.org/wiki/Dijkstra's_algorithm

Marshall

You mentioned that Vanderghast replied to my question in the previous thread

I must have missed it (I suspect that it is staring me in the eye but where??)


How does one see "previous" threads?

I did a quick search on the site to discover that Vanderghast name is pasted
right across so I assume that he is one of (probably the) good Samaritans
who run that site and if that is the case I would take the opportunity to
thank him and congratulate him for his effort (I am sure he is within earshot)


Cheers

David

 

[Marshall Barton]

You mentioned that Vanderghast replied to my question in the previous thread

I must have missed it (I suspect that it is staring me in the eye but where??)

How does one see "previous" threads?

I did a quick search on the site to discover that Vanderghast name is pasted
right across so I assume that he is one of (probably the) good Samaritans
who run that site and if that is the case I would take the opportunity to
thank him and congratulate him for his effort (I am sure he is within earshot)

The way my newsreader list threads is by date/time in
ascending order, so the "previous" thread is the thread that
was started just before your thread. I thought that might
be an issue so I posted the link to wikipedia that he cited
so you would not have to hunt around for his post.