Some questions:
Since the 3 tables have common key fields AcCode, how would you relate ACTRANS to ACTR2? I am thinking of a missing field in ACTRANS which is ACTRANSCODE.
>>>I've 3 tables :-
>>>
>>>ACGLMST -> General Ledger codes,
>>>Fields AcCode, Description, OpnBal
>>>
>>>ACTRANS -> Transaction file, Parent of Actr2,
>>>Fields AcCode, Date, Amt, DrOrCr
>>>
>>>ACTR2 -> Child of Actrans, 1 to Many Relation,
>>>Fields Actranscode, AcCode, Date, Amt, DrOrCr
>>>
>>
>>Let's assume that DrOrCr field contains either D for Debit C for Credit
>>
>>You can separate DR or CR columns by: IIF(DrOrCr='D',amt,0) AS Debit, IIF(DrOrCr='C',amt,0) AS credit etc., etc.
>>
>>>I need report in following format
>>>
>>>
>>>Ledger Code 1 "Scb Bank A/c" Opening Balance 145,000.00
>>>==================================================================
>>>
>>>AcCode Description Date DrAmt CrAmt
>>>
>>>12 Xyz Customer 01/01/99 50,000.00 -
>>>13 Claims 15/02/99 - 1560.00
>>>
>>>Ledger Code 2 etc. etc.
>>>
>>>
>>>
>>>By writing a procedure, i can create a cursor, but is there any way to retrieve this info with 1 sql command only.. (which I can use in report)...
>>>
>>>TIA
>
>Thnks for your reply.. i'm aware that iif can be used here but how to get this whole info. in cursor in one sql statement...
>
>Pls advs..
JESS S. BANAGA
Project Leader - SDD division
...shifting from VFP to C#.Net
CHARISMA simply means: "Be more concerned about making others feel good about themselves than you are in making them feel good about you."