Hello!

I am working with CrystalReports 8.5, for my VBProject.
I want to design the report as below specified.
I have 2 table namely 'AccountMaster' and 'Ledger'. the structure of AccountMaster is as below

AccountCode ..... AccountName ..... OpeningAmount ..... DrCr
---------------- --------------- -------------------- -------
101 ..... Ravi ........ 5000.00 ..... D
102 ..... Surya ....... 12000.00 ...... C

DrCr refers to 'Debit Amount' or Credit Amount.


and the LEDGER table as follows

TransType .... AccountCode ........ Amount ....... DrCr
------------ ---------------- ---------- ------
11 ......... 101 ......... 1000.00 ......... D
12 ......... 101 ......... 2000.00 ......... C
11 ......... 102 ......... 2500.00 ......... D
12 ......... 102 ......... 1500.00 ......... C


Now I want to display the report as follows using the above tables. (the report format should be as follows )


AccountName Debit Credit
---------------- ----------------- -----------------
Ravi ......... 4000.00 .........
Surya ......... ......... 11000.00

[ :: the above calculation should be done as

Opening balance(if Debit) + Sum of Debit amounts of ledger - sum of Credit Amounts of Ledger

Opening balance(if Credit) + Sum of Credit amounts of ledger - sum of Debit Amounts of Ledger
::]

Hint :


AccountName Debit Credit Balance
---------------- --------- ---------- ---------------
Ravi ......... 6000.00 ......... 2000.00 ......... 4000.00 ......... D
Surya ......... 2500.00 ......... 13500.00 ......... 11000.00 ......... C


I tried the below query in my data base.... then how can i make it short and how to call the same from query.

SELECT (SELECT SUPPLIER FROM ACCMASTER WHERE ACCMASTER.ACCCODE = LEDGER.ACCCODE) AS ACCOUNT_NAME, (SELECT SUM(AMOUNT) FROM LEDGER WHERE ACCCODE = 101 GROUP BY DRCR HAVING DRCR = 'D') AS DEBIT, (SELECT SUM(AMOUNT) FROM LEDGER WHERE ACCCODE = 101 GROUP BY DRCR HAVING DRCR = 'C') AS CREDIT, DEBIT-CREDIT AS BALANCE
FROM ledger
GROUP BY ACCCODE
HAVING ACCCODE = 101;

Thanks in advance..