Larry,
This has been a good discussion. I had already made tables as you suggested below prior to my thread. I just had to decide what to do with the ship date and how to properly create the open order reports (as well as other reports). I seem to have been on the right track. It does me good to see my logic has not completely left me. I have not used the MAX or left outer join function you show below. I am learning. Jim ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Lawrence Lustig Sent: Thursday, December 17, 2009 12:03 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: database design for reports << 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
