<< And you bring up the other problem I had when I was trying to figure this out myself a few years ago. You could ship partial quantities of an item. So one item could have multiple invoice#s / tracking info. >> In that case you need tables for:
OrderHdr OrderDtls ShippingHdr ShippingDtls and links like this: OrderHdr -> OrderDtls OrderHdr -> ShippingHdr ShippingHdr -> ShippingDtls OrderDtls -> ShippingDtls and a view like this: CREATE VIEW OrderDtlShipStatus (OrderID, LineNo, QtyOrdered, QtyShipped, QtyDue) AS + SELECT O.OrderID, O.LineNo, MAX(O.QtyOrdered), SUM(S.QtyShipped), (MAX(O.QtyOrdered) - SUM(S.QtyShipped)) + FROM OrderDtls O LEFT OUTER JOIN ShippingDtls S + ON O.LineID = S.OrderLineID + GROUP BY OrderID, LineNo to show you the shipping status for each line. (I'm just implementing the same thing right now from the purchasing / receiving side of the equation). -- Larry
