I'm sorry... u r right... through actranscode it relates actrans and actr2..

>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..