Hilmar:

Your SQL will sum all the deposits and withdrawas for each acct. That's not what I want. I want to show each individual withdrawal and deposit. If I have more withdrawals than deposits, then I want 0 to appear in the deposit field and vice versa.

See the resulting cursor.

Yossi



>>Hi:
>>
>>I'm trying to figure how to accomplish the following with SQL:
>>
>>I have two tables for a bank, one the customer master and the other a transaction detail:
>>

>>CUST
>>
>>ACCT
>>11111
>>22222
>>77777
>>
>>TRANS
>>
>>ACCT    AMOUNT    TYPE
>>11111   10.00     withdrawal
>>11111   11.00     withdrawal
>>11111   12.00     deposit
>>22222   20.00     withdrawal
>>22222   21.00     deposit
>>22222   22.00     deposit
>>22222   23.00     deposit
>>

>>
>>The resulting cursor should look thus:
>>

>>
>>ACCT    WITHDRAWALS   DEPOSITS
>>11111   10.00         12.00
>>11111   11.00         0
>>22222   20.00         21.00
>>22222   0             22.00
>>22222   0             23.00
>>

>>
>>How do you do that???
>>
>>Thanks for the help,
>>
>>Yossi
>
>

>select acct, sum(iif(type = "withdrawal", amount, $0)) as withdrawal,;
>  sum(iif(type = "deposit", amount, $0)) as deposit;
>  from MyTable;
>  group by acct
>

>
>HTH, Hilmar.