Larry,
Thanks for the suggestion. This is all good information for me so I can play with the options before implementing. I want that combination of simplicity and thoroughness so everyone is happy. Jim ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Lawrence Lustig Sent: Thursday, December 17, 2009 10:06 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: database design for reports << With your suggestion, I assume I would use the Order rows table as the primary table for this report (with open orders based on the backorder amount or remaining amount) and the header info would be brought in via lookup variables. Correct me if I am wrong. I know this is a basic question, but most of my NEW reports are just copies of existing ones so my memory fails me sometimes when starting from scratch. >> You would do: CREATE VIEW OpenOrders AS + SELECT * FROM Orders WHERE OrderID IN (SELECT DIST OrderID FROM OrderRows WHERE ShipDate IS NULL) or CREATE VIEW OpenOrders AS + SELECT * FROM Orders WHERE OrderID IN (SELECT DIST OrderID FROM OrderRows WHERE ShippingID IS NULL) (Depending on which solution from my original email you go with). Now, just copy your open order report so it is based on the view OpenOrders instead of the table Orders. You won't need to make any other changes to it. It will include unshipped and partially shipped orders. If you want a detail report (showing exactly which lines are left to ship) base it on OrderRows. -- Larry
