working backwards

  • Thread starter Thread starter Leo
  • Start date Start date
L

Leo

I was giving a set of data - distance between points and
the time for traveling between them - together with the
transit time basis two different modes of transportation -
air & car ... the info is presented individually as
a "point pair" -- it takes too long to find the data
sometimes and I'd like to work backwards and turn this
info in to data base where one can query by points to get
the transit the times ... how best to design the database??
 
I was giving a set of data - distance between points and
the time for traveling between them - together with the
transit time basis two different modes of transportation -
air & car ... the info is presented individually as
a "point pair" -- it takes too long to find the data
sometimes and I'd like to work backwards and turn this
info in to data base where one can query by points to get
the transit the times ... how best to design the database??
Click to expand...

A "Self Join" query linking the table to itself would seem to be the
best bet here. With proper indexing this can be reasonably fast; it's
best if each record contains a foreign key to the other member of its
pair.

Could you describe the current structure of your data?
 
sorry late reply - was traveling

have an updated source - its a "distance wheel" - one
smaller plastic wheel over another -- all are based on one
city - NOLA- being the origin city -- on the out side of
the "bottoM" wheel are 20 cites - on the top wheel there
are 3 cut out boxes on top of each other - they are
for "truck" - "Train" and "Barge" -- in each is gives you
the transit days as well as the mileage -- for "barge"
there are split transit times for north & south bound due
effect of river current - I have two sets of
these "wheels" that give me 40 port "pairs" -- I'd like
to set up a database to be used on a web site where a
visitor can queury for distances/ttime for his particular
port pair(s)
 
have an updated source - its a "distance wheel" - one
smaller plastic wheel over another -- all are based on one
city - NOLA- being the origin city -- on the out side of
the "bottoM" wheel are 20 cites - on the top wheel there
are 3 cut out boxes on top of each other - they are
for "truck" - "Train" and "Barge" -- in each is gives you
the transit days as well as the mileage -- for "barge"
there are split transit times for north & south bound due
effect of river current - I have two sets of
these "wheels" that give me 40 port "pairs" -- I'd like
to set up a database to be used on a web site where a
visitor can queury for distances/ttime for his particular
port pair(s)
Click to expand...

I'd think you could do this with two tables: a table of Ports, with 20
rows; and a table of distances like:

Distances
Port1 <link to the Primary Key of the Ports table
Port2 <another link to the Primary Key of Ports
Mode <"Barge", "Truck", "Train"
Distance
Time

You'ld need to fill this in manually from your wheels if you can't get
the information into the table otherwise - 380 rows per wheel, so it's
not overwhelming.

Given this table it would be very easy to create a Query allowing the
user to choose an origin port, a destination port, and a mode of
transit to retrieve the distance and time.
 
Thanks much! Had figured on entering all of this manually -- any comments re barge in showing two transit times - north and south bound?
 
Thanks much! Had figured on entering all of this manually -- any comments re barge in showing two transit times - north and south bound?
Click to expand...

That's taken into account: in the table structure

Distances
Port1 <link to the Primary Key of the Ports table
Port2 <another link to the Primary Key of Ports
Mode <"Barge", "Truck", "Train"
Distance
Time


you would have two records, one with Amsterdam as Port1 and Freiburg
as Port2, the other with Freiburg as Port1 and Amsterdam as Port2.
They would have the same distance but different times (at least for
Barge travel!)
 
John / Leo

thanks your reply and sorry late reply - have been traveling and am taking off again tomorrow..

sorry but don't see it in the table structure. Truck and train will only have a single transit time -- these modes don't differ north or south ... but barge is effected by current of river and thus you have two transits times based on "direction" -- am guessing will have to have an additional field in the distance table?? this field will be null for truck and train?!
Click to expand...

The table has a Port1 and a Port2 field SO IT IS DIRECTIONAL. There
will be *six records* for each combination of Ports: three from PortA
to PortB, three from PortB to PortA. Four of these will be redundant,
but it sounds like you're dealing with some forty ports - so the size
of the table will be *tiny* by Access standards.

For instance, you might have records such as:

PortA PortB Truck 300km 3.5hr
PortB PortA Truck 300km 3.5hr
PortA PortB Train 289km 2.9hr
PortB PortA Train 289km 2.9hr
PortA PortB Barge 318km 4.5hr <downstream>
PortB PortA Barge 318km 6.1hr <upstream>
 
Back
Top