report in bank statement format

[cliff]

Hi, I have small problem in displaying report in bank statement format. My
data tables something like loan disbursal and periodical repayments of
interest and installments

In short One loan disbursal entry for loan, whereas mutiple repayments on
interest and instalments on same and different dates.
my data is :-

tbldisbursal
lnno cdate amount
5481 1/1/1999 405000
5480 1/2/1999 360000

tblpayment

paycode lnno paydate amount
1 5481 1/4/1999 500
2 5481 1/4/1999 5000
1 5480 1/4/1999 450
2 5480 1/4/1999 4000
2 5480 1/4/1999 10000
2 5480 1/4/1999 5000
1 5481 1/5/1999 500
2 5481 1/5/1999 10000

tblpaycode

paycode discription
1 interest
2 instalment

Now I want to display statement of a/c

LNNO 5481
cdate particulars interest installment balance
1/1/1999 Loan disbursed 405000 405000
1/4/1999 Interest 500 405000
1/4/1999 instalment 5000 400000
1/5/1999 Interest 500 400000
1/5/1999 instalment 10000 390000

Balance Rs.3,90,000/-

LN 5480
cdate particulars interest installment balance
1/1/1999 Loan disbursed 360000 360000
1/4/1999 Interest 400 360000
1/4/1999 instalment 4000 356000
1/4/1999 instalement 10000 346000
1/4/1999 instalment 5000 341000

Balance Rs.3,41,000/-

please help solve this

thanks million

 

[Allen Browne]

If there are only a couple of paycodes, you could create calculated fields
in a query. Type expressions such as this into the Field row:
Interest: IIf(paycode=1, [amount], Null)

If there are numerous paycodes, you could use a crosstab query that uses
both tblPayment and tblPayCode, where:
- tblPayCode.discription (group by) is the Column Heading
- tblPayment.amount (sum of) is the Value.
- other fields (group by) are the Row Headings.

 

[cliff]

Hi Allen Browne,

thanks for help, I followed your suggestions and it helped to put interest
and instalement in different columns. But now I want get loan disbursal
amount from
tbldisbursal into my statement and I want to show runnning balance of loan
disbursal amount minus (-) instalment paid as balance.


Could you help me solve this

 

[Allen Browne]

Make the appropropriate figure negative by adding a minus in front of the
appropriate expression.

Then use the Running Sum property of the text box in the report to show the
balance.

 

[cliff]

Hi "Allen Browne"


thanks for prompt reply. I have problem in getting data from tbldisbursal
ie., loans disbursed amount into my statement . could you tell me how do I
connect data from tbldisbursal and tblpayment to arrive running balance in my
report as shown below :-


thanks millions

cliff

 

[Allen Browne]

If you want to intersperse the values, use a UNION query.

This kind of thing:

SELECT Inno,
[cdate] As TransDate,
amount As HowMuch,
"Disbursal" As source
FROM tbldisbursal
UNION ALL
SELECT Inno,
paydate As TransDate,
-[amount] As HowMuch,
"Payment" As source
FROM tblPayment
ORDER BY TransDate;

 

[cliff]

Hi "Allen Browne"

thank you very much for help. I have no words express my gratitude. Yes
yours help/ hints working very well. I managed to prepare running statement
all because of your help.


Now I want to :-

1)perpare report only for particular period say 1st April 1999 to 31st
March 2001.
2)report for particular Loan A/c say LN 5481

How do I do that. Please guide me.
LNNO 5481
Statement of A/c for the period 1st April 1999 to 31st mach 2001
cdate particulars interest installment balance

Opening Balance as on 1st April 1999 40500

1/4/1999 Interest 500 405000
1/4/1999 instalment 5000 400000
1/5/1999 Interest 500 400000
1/5/1999 instalment 10000 390000

closing Balance as on 31st March 2001 Rs.3,90,000/-


thanks millions

cliff

 

[Allen Browne]

Now that you have the report, working, you can filter it with the
WhereCondition for OpenReport.

Here's a simple example that shows how to limit one to one record:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

Here's one that handles a range of dates:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

And here's a more comprehensive example, that shows how to build a filter
string based on a combination of values, partial matches, and ranges, for
different data types:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
This one returns the results in a form, but the process is identical to
create the filter string for a report.