Albert,
Thanks for the excellent feedback. I'm going to save this message for when the work is a little further down the road than we are right now. _____ From: [email protected] [mailto:[email protected]] On Behalf Of Albert Berry Sent: Wednesday, April 01, 2009 10:04 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: There should never be more than one, but... The CID is an attribute of the invoice, but can also be an attribute of the payment. Consider company A buys company B and now pays a company B invoice. In a recent project of mine, the A/R model came out along these lines. This might help you put your 20 year old project together. That's roughly what this project was, converting old data in a badly designed database to a reasonably proper SQL design, and bringing it from 6.1a DOS to 7.6 Win, and adding quite a few enhancements. (Yes, R:Azzak, we are current!) Tables and constraints, greatly simplified subset. * Customers PK CustID * Invoices PK InvNo * FK Customers.CustID * CustChecks PK CustCheckID * FK Customers.CustID * Receipts PK ReceiptNo * FK Invoices.InvNo * FK CustChecks.CustCheckID The receipts table links the check to the invoice, but allows for many checks paying on one invoice, many invoices on one check, or any combination, and because the receipts is at the lowest level, we can have unapplied and/or partially applied checks, such as when we get a deposit or the customer is pre-pay. A simple view suffices to identify any checks that have not been 100% applied to invoices, or that have been overapplied, by linking the checks and receipts using an outer join. We can also have partially paid invoices. By having the checks in their own table, we can also group invoices and credit notes on a check so that they disappear from the A/R balances, as they should. Just a few thoughts, having gone through the exercise! Albert Gray, Damon wrote: I think you may have a data design flaw here. The CID is an attribute of the Invoice, not the Payment. BTW, you're absolutely correct in your assertion above. The DB and code I'm working on was set up and written roughly 20 to 25 years ago. Most of what I'm doing is cleaning it up while charting a course out of these shark-infested waters. The data is nowhere near anything that even pretends to be normalized. I'm a novice DB coder, and even I understand enough to know that this was very poorly designed, but there is a wealth of data in there (corrupt though it may be) and part of my job is to start where we are, and keep us afloat while planning a brighter future!! :-) Thanks again to everyone for the consistent support and positive feedback. _____ From: [email protected] [mailto:[email protected]] On Behalf Of Lawrence Lustig Sent: Wednesday, April 01, 2009 2:01 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: There should never be more than one, but... << I'm trying to get a count of customer IDs (CID) tied to an invoice number. There should never be more than one CID per invoice, but these can appear multiple times in the table as customers pay on an invoice and payment entries are made. For example, there are three payments by Client 105 on Invoice 137295. So, I want to run a query to get that count, but the first below is giving me a value of 3, and the 2nd lame attempt doesn't give me anything but an error. How should the syntax read for this? select count(CID) into vMyCIDCount from ARTRANS where INVOICE# = .vINVOICE# (Gives me 3) >> I think you may have a data design flaw here. The CID is an attribute of the Invoice, not the Payment. You can get your count using: select count(DISTINCT CID) into vMyCIDCount from ARTRANS where INVOICE# = .vINVOICE# And you can find ALL invoices that are messed up in this way using: SELECT Invoice#, COUNT(DIST CID) FROM ARTrans GROUP BY Invoice HAVING COUNT(DIST CID) > 1 -- Larry
