* 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
