asked on

pleas help to make a Query

hi,
could anyone help me to make a query. My table in Microsoft Access
here's my table Structure.

TCode      Text      10
TrnNo      Number
TrnDate      Date
Amount      Number
DCFlag      Text      1

i'd like to make a query for below mentioned result. In this result, if the DCFlag='C' the amount should come under 'Credit' and the DCFlag='D' the amount should come under 'Debit'.

TCode      TrnNo       TrnDate        Debit Credit
------------------------------------------------------------------------
ABC      001      10/02/2003 100
ABC      005 12/02/2003        75
XYZ      002      11/02/2003 1570
XYZ      003      12/02/2003        1570

in advance, thanks so much for ur great help.

Wigz

select TCode, TrnNo, TrnDate, iif(DCFlag= 'D', Amount, 0) as Debit, iif(DCFlag, 'C', Amount, 0) as Credit
order by TCode, TrnNo

This will return numeric values in the Debit and Credit columns. You could replace the 0 with null if you want it to show spaces eg iif(DCFlag='D', Amount, null) as Debit.

ASKER CERTIFIED SOLUTION

Wigz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

satdream

ASKER

dear friend
Thankx so much for ur kind help....... i had only 50 points. that's hwy i gave 50 points. u deserve more than that....