I have to make a report based upon this scenario...seems simple, yet I can't get the numbers right....any pointers? (I have ordered a newer Advanced SQL book in the meantime!!)
Need to get orders within date range containing a part number and get total dollars received in payments for this. So I query the orders table....which is related to the order_items table, where each line item of the order is stored, but does include a line_total field which did qty * rate charged. On the other hand, the payments table is related to the orders table by order id, and shows amount paid...but obviously not breaking out what of the payment applies to what. So how do I query to get the applicable payment amount correct by that item id? Right now my numbers are inflated because it gets the whole order payment amount. Recievables higher than sales is great in fantasyland. Mucho frustrating no doubt. Thanks guys. Eric ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191535 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54