<< 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
