>Hi Naomi
>
>Firstly congratulations for the award this year too and I see you have it all 3 categories, you earned it. Thanks for the help you have given me too.
>
>>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,
>
>I know you have this resolved so that is great, but I can't resist the comment, you are only SUM()ming NULLs above, how about the non-NULL records and could a GROUP BY on account help you with the query.
Thanks again. A bit tired today, but I could not figure out how to properly sum accounts and not payments. If you group by Account Number, would it still work? I guess it would, don't know why I didn't try when I was looking at this statement today...
If it's not broken, fix it until it is.
My Blog