I will buy the coffee if this goes on (next SAT I attend). You all are getting close to a problem, I have. Razzak, Lawrence, Jim, Bill, Karen, Emmitt, & ALL of you, your examples are very very helpful.
I have shipments w/dates of receipt and return and another table with charges per unit/month and a table that is less x-amount qty based of qty on hand. Ie: Recv 3/2/04 returned 6/8/09 what were the charges 2004 monthly was 78/m, 05 was 89/m, and 06 was 72.50/m etc.. but the first 4 are free if qty > 500 etc. I have 50,000 rows of this. On demand reconcile for current inventory and retain current totals and history. What charges would have been over the last 8, 6, or 2 years depending on return date. And credits from another table to check if a proper credit was applied (Not) and reconcile. Sincerely, Paul D. My problem, but you all are giving me gReat ideas while I review emails. Thank you for helping. All too confusing to me, but I am working on it w/9.0 and have a goal. ;)) Inner, Outer Join - great reminders not to mention more examples. From: [email protected] [mailto:[email protected]] On Behalf Of Doug Hamilton Sent: Thursday, December 17, 2009 1:08 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: database design for reports Thanks, Karen - I got the coffee. Partial shipments is why I mentioned a third table: maybe one table with OrderRowID (unique autonumber ID for each row in OrderRows), OrderNumber, ShipDate QtyShipped &whatever else might be necessary. When you ship, add rows to this table. With prudent indexing, use SQL to find what's been shipped and what hasn't. You can add any number of rows for a specific item, each with a ship date and qty. In spite of the coffee, I still haven't thought this through all the way. OT from my son: An SQL query walks into a bar, approaches a couple of tables and asks "May I join you?" Doug [email protected] wrote: Doug: yes, I think you SHOULD get your coffee now. 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. That's what made my head spin and I never implemented a more accurate shipping record. Karen Oops, you're right, my error. The Order Detail table gets the invoice #, not the Order Header. But even that would cause problems if there were multiple (partial) shipments for an item resulting in multiple invoices. Maybe the invoice number isn't even needed in the Order Detail; I did have the order number in the invoice header table, so it could done with a view - I'd have to look at that old db, it's been years. Can I go get my coffee now? :) D
