Randall,

Note the use of < pre > and < /pre > (without spaces) to format code. ;-)

Usually you would have a table of Banks and use a LEFT JOIN to pick up activity in your time period.

SELECT Banks.BankName, ;
    SUM(IIF(INLIST(TranCode, cCredit1, cCredit2, cCredit3), ;
    Collections.Amount, $0)) AS Credit_amt, ; 
    ... ;
    FROM Banks LEFT JOIN Collections ;
    ON Banks.BankId = Collections.BankID ;
    AND BETWEEN(Collections.SetDate, m.dStartDate, m.dEndDate)

Note the use of $0 in the SUM to maintain the Currency data type. SQL uses the type and size of the first record that meets the condition. Be prepared for your first record to be a 0.


>I am using the following SELECT to get deom data I need.
>

>SELECT collections.settdate, collections.bankname, collections.portfolio, ;
    MONTH(collections.settdate) AS mymonth, ;
    SUM(IIF(BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) AND (trancode = cCredit1 OR trancode =cCredit2 OR trancode =cCredit3), 1 , 0)) AS credits , ;
    SUM(IIF(BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) AND (trancode = cCredit1 OR trancode =cCredit2 OR trancode =cCredit3), collections.amount ,0.00)) AS credit_amt, ;
    SUM(IIF(BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) AND (trancode = cDebit1 OR trancode =cDebit2 OR trancode =cDebit3), 1 , 0)) AS debits , ;
    SUM(IIF(BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) AND (trancode = cDebit1 OR trancode =cDebit2 OR trancode =cDebit3), collections.amount ,0.00)) AS debit_amt, ;
    SUM(IIF(BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) ,1,0)) AS tot_num, ;
    SUM(IIF(BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) ,collections.amount ,0.00)) AS tot_amount ;
    FROM collections ;
    WHERE BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) and collections.portfolio= cBank ;
    GROUP BY  collections.settdate, collections.bankname ;
    INTO TABLE eraseme2

>
>If one fo my banks is not in the specified date period, I get nothing which i think is right. I want to get all zeroes for a bank not in the date sequence for example, FUNB is missing and I get FUNB credits "0"
>
>Is there a simple way to do this?
>
>thanks in advance for your help.
>
>Randall