Finance Statement problem?

[ontherun]

Hi all,

I am using MS Access 2002. i am building a database for a Finance Company
I have 2 tables - tblCustomer (personal Details and PK is LoanNumber)
tblLoan (PK is LoanID)

Relationship is 1-to-many.

With the information i have gained so far from TSDN forum, i could write some VBA code for my database. I close to finish building my database but i was stuck in doing the report.

I need to create a report that shows all the payments made in terms of Principal Loan Amount and also for all interest paid between 3 month (say january to march).
i am trying to achieve similar to a bank statement.

I have created a parameter query using fields from both the tables. the resulting query is based on the value entered by the user for LOANNUMBER.

Once the report has been generated, the user needs to make some changes manually. i could not edit a report.

So i came to know it is only possible to transfer the results to word and then make the changes before printing.

Is it possible, to have the same format in word?

How should i transfer the fields from a report to word???

I wanted to retain the same structure in word as in the report. the rtf format misses the tables, lines and logo but is there any way i could acheive some editable report?

I am scared it may include lot of coding...but anway, its nothing to learn though. :).
Need someone's assistance.

thanks
Catherine

[Rabbit]

Hi all,

I am using MS Access 2002. i am building a database for a Finance Company
I have 2 tables - tblCustomer (personal Details and PK is LoanNumber)
tblLoan (PK is LoanID)

Relationship is 1-to-many.

With the information i have gained so far from TSDN forum, i could write some VBA code for my database. I close to finish building my database but i was stuck in doing the report.

I need to create a report that shows all the payments made in terms of Principal Loan Amount and also for all interest paid between 3 month (say january to march).
i am trying to achieve similar to a bank statement.

I have created a parameter query using fields from both the tables. the resulting query is based on the value entered by the user for LOANNUMBER.

Once the report has been generated, the user needs to make some changes manually. i could not edit a report.

So i came to know it is only possible to transfer the results to word and then make the changes before printing.

Is it possible, to have the same format in word?

How should i transfer the fields from a report to word???

I wanted to retain the same structure in word as in the report. the rtf format misses the tables, lines and logo but is there any way i could acheive some editable report?

I am scared it may include lot of coding...but anway, its nothing to learn though. :).
Need someone's assistance.

thanks
Catherine

You could start with a template and the use code to fill in the template but that requires quite a bit of coding.

What do the users need to edit that can't be done in Access? Maybe there's a way to do it in Access that you don't know about.

[ontherun]

You could start with a template and the use code to fill in the template but that requires quite a bit of coding.

What do the users need to edit that can't be done in Access? Maybe there's a way to do it in Access that you don't know about.

thanks for your reply Rabbit.

the actual statement comprises of 2 tables. First table shows details about the Loan Amount paid by the Customer. And the second table shows details about the Interest based on the Loan for each month.

the size of the table increase/decrease with respect to the information for each rows.

before taking the print out, the user want to change something in the description part either for Loan or Interest Table.

As of now my report is showing only the Interest information for each month in a Table format.
how can include both Principal and Interest information in the same report?

and how i can transfer the the report from Access report to MS Word ???

It will be of great help if you could assist me in this regard.

thanks,
Catherine

[Rabbit]

thanks for your reply Rabbit.

the actual statement comprises of 2 tables. First table shows details about the Loan Amount paid by the Customer. And the second table shows details about the Interest based on the Loan for each month.

the size of the table increase/decrease with respect to the information for each rows.

before taking the print out, the user want to change something in the description part either for Loan or Interest Table.

As of now my report is showing only the Interest information for each month in a Table format.
how can include both Principal and Interest information in the same report?

and how i can transfer the the report from Access report to MS Word ???

It will be of great help if you could assist me in this regard.

thanks,
Catherine

If they want to change something in the description field, can't they just do this in a form? No need to export the data to word and then change it.

You can include both on the same report by using subreports.

As far as I know you can't transfer a report to Word.

[ontherun]

If they want to change something in the description field, can't they just do this in a form? No need to export the data to word and then change it.

You can include both on the same report by using subreports.

As far as I know you can't transfer a report to Word.

I have a description field for each payment and i hav include the same in the report.
but they felt it will be better if there is any option so that they can edit ,if any, before taking a printed copy.

i have only two tables - Customer and Loan.

the Loan table has got both Loan Amount Information and also Interest Information.

My Statement must have following format:

Heading

Customer Address


A table showing Information about all transaction made against LOan Amount
Fields for Loan Payment Table:
Date Description Debit Credit Balance


A table showing Interest information for each month. Fields for Interest Table:
IntrerestDate Int_Description Int_Debit Int_Credit Int_Balance


All the above fields for both table are from Loan table.
Only the Address field is from the Customer Table.

Both Tables are linked through the field Loan Number

is it possible to acheive ???
Need someone's assistance..

thanks n regards,
Catherine

[Rabbit]

I have a description field for each payment and i hav include the same in the report.
but they felt it will be better if there is any option so that they can edit ,if any, before taking a printed copy.

i have only two tables - Customer and Loan.

the Loan table has got both Loan Amount Information and also Interest Information.

My Statement must have following format:

Heading

Customer Address


A table showing Information about all transaction made against LOan Amount
Fields for Loan Payment Table:
Date Description Debit Credit Balance


A table showing Interest information for each month. Fields for Interest Table:
IntrerestDate Int_Description Int_Debit Int_Credit Int_Balance


All the above fields for both table are from Loan table.
Only the Address field is from the Customer Table.

Both Tables are linked through the field Loan Number

is it possible to acheive ???
Need someone's assistance..

thanks n regards,
Catherine

This is more than possible to achieve in one query/report. You just have to join all the tables together. I can't say how it'll be done for yours because I don't know how the tables are laid out but from what you tell me, it sounds more than possible.

As for editing the report before printing. Just have them edit the information in a form before opening the report, there's no need to go through the extra trouble of exporting to a word document.

[ontherun]

This is more than possible to achieve in one query/report. You just have to join all the tables together. I can't say how it'll be done for yours because I don't know how the tables are laid out but from what you tell me, it sounds more than possible.

As for editing the report before printing. Just have them edit the information in a form before opening the report, there's no need to go through the extra trouble of exporting to a word document.

Thanks for your reply Rabbit.

I will let the users to make the changes in form before printing.

but i am still looking for an answer to include 2 tables in the same report.
the fields corresponding to both tables are from the same table tblLoan.

thanks again for your effort.

regards,
Catherine

[Rabbit]

If I understand correctly, your tables are laid out like this:

Customer Table
Loan Number; Key
CustomerAddress; String

Loan Table
Loan Number; Key
All Other Fields

You can just make a query and join it on Loan Number.

[ontherun]

If I understand correctly, your tables are laid out like this:

Customer Table
Loan Number; Key
CustomerAddress; String

Loan Table
Loan Number; Key
All Other Fields

You can just make a query and join it on Loan Number.

Customer Table - Primary Key is Loan Number
Address Fields


Loan Table - Primary Key is Loan ID
Foreign Key is Loan Number.
Loan Amount, PreviousPaidLoan,LoanPaidDate,LoanBalance.
Interest,InterestPaidDate,InterestPaidAmount,Inter estBalance


the fields i want to be listed in a 2 seperate tablular format are from Loan table.

in the report, i want 2 tables . one showing information about Loan for each month
Other table showing Interest information for each month.

i dont have problem in getting one information.

is it possible to show these information in the report as two different tables.??

thanks and regards,
Catherine

[Rabbit]

Yes, like I mentioned earlier, use subreports. Make 2 reports and include them in as subreports in one report.