Crystal Report - from 2 tables

[satyarao]

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

[tvssarma]

This can be done using formulas and summing fields judisously. The problem is that I cn't explain the whole process here since it requires a lot of graphics.
You better refer to the Crystal rep help

[Pasvorto]

I guess I would design the report in Crystal and call it from VB. I think you could do it fairly easily within Crystal and not need such a complex SQL statement.

[Pasvorto]

Select each transaction, connect the two tables on acct code. Create a group, based in account code. Hide the deatil records. Show the name in the group. Get a subtotal of the debit transactions and a subtotal of the credit transactions. Create a formula for debit and one for credit in the group header. For the debit formula it would se something like.. if opening balance = "D" then opening amount + subtotal of debits - subtotal of credits. Do the same thing for the credits.