>>It's not really an accounting report, the report is called "synopsis". We got almost all numbers sum up fine except for one category which is a bit off and it's very hard to reconcile what fell into the cracks.
>
>What category?
Is there a way to get sum for accounts and not payments in this kind of the select statement
SELECT COUNT(distinct myField) as iAccounts, ;
SUM(IIF(ISNULL(dPosting_Date) AND iClosed = 1,1,0)) as iClosedNoPayments, ;
SUM(IIF(ISNULL(dPosting_Date) AND iClosed = 1,0,1)) as iClosedWithPayments, ;
SUM(IIF(ISNULL(dPosting_Date) AND iClosed = 0,1,0)) as iOpenNoPayments, ;
SUM(IIF(ISNULL(dPosting_Date) AND iClosed = 0,0,1)) as iOpenWithPayments,
Count distinct works, but I also want sum to be for accounts and not for payments.
If it's not broken, fix it until it is.
My Blog