Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

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.
0
satdream
Asked:
satdream
  • 2
1 Solution
 
WigzCommented:
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.

0
 
WigzCommented:
whoops - left out the table. That should be

select TCode, TrnNo, TrnDate, iif(DCFlag= 'D', Amount, 0) as Debit, iif(DCFlag, 'C', Amount, 0) as Credit
from tablename
order by TCode, TrnNo
0
 
satdreamAuthor Commented:
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....
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now