Multi-Stop Solution

[Guest]

I defined "Shipper" and "Receiver" as companies that are shipping or
receiving product. I defined "Truck" as an equipment that is transporting
product from Shipper to Receiver. I also defined "Load" as an event where is
used particular Truck to transport product from particular Shippers to
particular Receivers.

Problem that I have is that number of Shippers or Receivers in one
particular Load can vary. I plan to define "Sequence of Shippers" where would
be listed all Shippers for particular Load. It would contain AutoNumber
(primary key), ShipperID (1st shipper), ShipperID (2nd shipper), ...
ShipperID (10th shipper) where I would assume that one load is not going to
have more than 10 shippers.

Is there any other way to resolve this where I would save only number of
Shippers that I really need, not static table with predefined number of max
Shippers - in this example 10?

Thank You

 

[Allen Browne]

There are several ways to model this.

One flexible approach would be to use a table with fields like this:
ID AutoNumber (primary key)
Quantity number loaded (positive) or unloaded (negative)
GoodsID what is being transported
ClientID foreign key to Client table: who supplied/received
LocationID where it was loaded/unloaded
VehicleID what vehicle is being used to transport this.

You can now enter records like this:
* 10 tons of a lawn fertilizer were loaded onto vehicle 74 from Acme
Corporation in St Louis.
* 2 tons of lawn fertilizer were unloaded from vehicle 74 to Bunnings
Hardware in Joplin MO.
and so on.

You can then calculate what quantity of what products are still on the
vehicle.

You may well need something more than the above: specifying particular
journeys and stages, and assuming the vehicle is empty at the beginning and
end of each journey. Depends on what kind of transportation you need to
handle.