Using MS query part 2

[Ian@1904]

Another little gem of a question, still using MS Query.

I have a set of transactions - accounting journals hence debits and credits.
The transactions are listed so that the positive and neagtive value all appear in the same column. What I want to achieve is splitting the values out.

If I was using Access I could extract the debit value from one query, and the credit(negative values) from another and then combine the two queries. MS query does not appear to allow me to do this.

Any ideas how to achieve this is MS query?

Nominal code Ref Debit Credit
1004 JNL1 45
1005 JNL1 55
1006 JNL1 -100

Aplogies on screen layout the -100 should be appearing under credit

[tootricky]

How about something along the lines of:

SELECT id, ref, IF trans_val < 0 THEN trans_val ELSE null AS debit, 
IF trans_val > 0 THEN trans_val ELSE null AS credit 
FROM transactions

NB: this is off the top of my head so not sure it would work in MS Query.

Cheers/Nic