Inbound and Outbound relationship logic

[SharonInGa]

Still stuck....

A truck (truckId.field) drives into my warehouse today (date.field) The
same truck can make a dilervery next week so the truckId and Date determines
the unique ID. The TruckID, Date and inventory data is collected on the
Inbound Form. The merchandise from this truck and many other trucks are
dumped onto our parking lot and the truckID isn't important anymore.

We are now more concerned with where the merchandise is going. We pick up
several boxes from our parking lot, document each box’s PO#, outbound
destination, weight and other info and put it onto a truck going to
Birmingham. We combine the PO# and Outbound destination to create a unique
ID#. We pick up some more boxes and put it onto a truck going to Miami.

I don't know whether to establish a relationship between the Inbound Truck
table to the Distribution table or the Destination table. The Inbound form
includes the truck number and the ship date on the main form and the box
descriptions on the sub-form.(one to many) The Outbound form includes the
Destination on the Main form and the box descriptions of the sub-form (one to
many) in addition to it’s own data.. PO#s, Box Weight, Received date and
other info are transferred from the Inbound main and sub forms to the
Outbound form.

 

[John Vinson]

I don't know whether to establish a relationship between the Inbound Truck
table to the Distribution table or the Destination table.

Probably neither.

The inbound TRUCK isn't related to the distribution, nor to the
destination. The boxes on the truck are.

What you're moving around is *the boxes* - each box should be an
entity in its own right; there's a many to many relationship between
boxes and trucks - each box will be on two trucks (one inbound, one
outbound) and each truck (and each truckload) will have many boxes.

John W. Vinson[MVP]

 

[SharonInGa]

John,
By the way, I've been using the Access newsgroup for over two years and
your explanations are always helpful.....

I get the Truck-boxes concept (many to many). Now, we also need to be able
to select an outbound destination and see a list of boxes that went to that
destination.

So far, does the logic look like this? Where do we stick in the
relationship for boxes to destination?

Truck Table ---- link table ------ Box Table

 

[John Vinson]

John,
By the way, I've been using the Access newsgroup for over two years and
your explanations are always helpful.....

I get the Truck-boxes concept (many to many). Now, we also need to be able
to select an outbound destination and see a list of boxes that went to that
destination.

So far, does the logic look like this? Where do we stick in the
relationship for boxes to destination?

Truck Table ---- link table ------ Box Table

Probably in the Truck table - which (if I understand aright) really
should be a Trip table, since you really don't care (do you?) what the
VIN number or license plate of the physical truck might be.

If your Trip table had a TripID; a field for Inbound or Outbound; a
field WhenceOrWhither which is the origin if it's inbound and the
destination if it's outbound - you should be able to get this
information readily.

John W. Vinson[MVP]