title: linking child tables of two related parent-child tables
body: <p>I have two parent child tables and a couple of supporting tables. I am struggling to figure out the right relationships between them.</p>
  
  <ol>
  <li><strong>Orders</strong>, a table that holds a list of Orders including:<code>Order Id, Supplier, Order Date</code> etc..</li>
  <li><strong>OrderDetails</strong>, a Table holds a list of products that have been ordered, and is a child of Orders, linked on OrderId. Key fields are the <code>ProductId and the Quantity Ordered</code>. Each order containes one or many OrderDetails that outline the products and quantities being ordered.</li>
  <li><strong>Shipments</strong>, a table that holds a list of Shipments, including <code>Tracking Number and Shipper</code>. Links to <strong>Orders</strong> through Order Id. An Order may have multiple shipments. For example, I ordered 100 lightbulbs. They were dispatched in 5 shipments.</li>
  <li><strong>ShipmentDetails</strong>, holds a list of product and shipped quantities and is linked to the Shipments table by ShippingId. Each Shipment may have multiple ShipmentDetails. i.e. One shipment may have 30 Lightbulbs and 10 Door Knobs.</li>
  <li><strong>Products</strong> is a table that holds a list of Products that need to be both ordered and shipped.</li>
  </ol>
  
  <p>So the logic is that I enter details about the products that are to be ordered in <strong>Products</strong>. For example, 100 CREE LED Lightbulbs, and 50 Door Handles.</p>
  
  <p>When I place an order, I create an Order in <strong>Orders</strong>.
  <code>i.e. amazon.com, order #45454.</code> </p>
  
  <p>Then I add child rows to that order in <strong>OrderDetails</strong>. <code>i.e. 30 CREE LED Lightbulbs.</code></p>
  
  <p>When the order Ships, I create an entry in the <strong>Shipments</strong> table. <code>i.e. Tracking #46464646464</code>, linked to OrderId in Orders. And then I enter what is in that shipment in <strong>ShipmentDetails</strong>. For example, only 20 or the 30 CREE LED Lightbulbs may be associated with this Shipping entry.</p>
  
  <p>I am struggling with figuring out how to relate the Shipping Detail records to the Order Detail Table. Lets say the Shipping Detail Table has 4 fields.</p>
  
  <ol>
  <li>ShippingID - a link to the Shipments table parent.</li>
  <li>TrackingNum - a field that holds a tracking number.</li>
  <li>Product - this should be a drop down, that says, the shipment I belong to is related to a defined Order (because ShipmentDetail is a child of a Shipment record which in turn holds a key to the Orders table, which in turn has OrderDetails that reference products).</li>
  <li>Quantity - this should have a default (overridable) value that is the "Quantity Ordered" number from the OrdersDetail record that matches the Order Id and Product Id. Where OrderId is in the Shipments table (linked to the Orders table) and ProductID comes from #3 above. The reason it must be overridable is that a shipment may be a partial shipment.</li>
  </ol>
  
  <p>I am stuck with the preceding #3 and #4. I hope I have explained this in a vaguely understandable way! The pictures below may help!</p>
  
  <p>The ask:</p>
  
  <ul>
  <li>What is the correct join between the <strong>ShipmentDetails</strong> and the <strong>OrderDetail</strong> Table</li>
  <li><p>How do I create a field in the <strong>ShipmentDetail</strong> table with a default value pulled from the Quantities Field of the <strong>OrderDetail</strong> table, where </p>
  
  <pre><code>Shipments!OrderId = Orders!Id and ShipmentDetail!ProductID = OrderDetails!Product ID
  </code></pre></li>
  </ul>
  
  <p>I am working in MS Access 2016 - but I suspect this is a fairly generic SQL question...rather than MS Access specific.</p>
  
  <p><a href="https://i.stack.imgur.com/9t1Bh.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9t1Bh.jpg" alt="enter image description here"></a>
  <a href="https://i.stack.imgur.com/hcQUC.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hcQUC.jpg" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/nhS6g.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nhS6g.jpg" alt="enter image description here"></a></p>