[AccessD] Design Pointers - Mapping Routes

[David Emerson]

Hi Listers,

A table storing travel routes has fields for two points.  Data might look
like this:

Point 1		Point 2
A		B
A		C
B		C
B		D
C		D
C		E
E		D

As can be seen from the last record, the order of the point data may not be
lowest/highest (it would be easier if the record could be entered as D/E but
this is not the case).  There will however only be one combination of points
(eg since there is a record for E/D there cannot be a record entered for
D/E).

Questions to be answered from the data could be:
1) What are the possible routes from A to C (without covering the same
section twice)?
Answers:
A-B, B-C
A-C

2) What are the possible routes from A to D (without covering the same
section twice)?
Answers:
A-B, B-C, C-D
A-B, B-C, C-E, E-D
A-B, B-D

3) What are the possible routes from A to E (without covering the same
section twice)?
Answers:
A-B, B-C, C-D, D-E (note that D-E is the E/D record)
A-B, B-C, C-E
A-B, B-D, D-E (note that D-E is the E/D record)
A-C, C-D, D-E (note that D-E is the E/D record)
A-C, C-E

To get the answers it seems that I will need to use queries to come up with
the different permutations for each record (eg E/D and D/E) so I can get all
the matches of end point to start point values (and I would also need to
check that the same route isn't used more than once)

Is there a better table structure to achieve this?

Can anyone point me in the right direction as to any theory behind this type
of mapping, and any code that might help generate the solutions?

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand