Solved

Loan Amortization and a tmp query

Posted on 2006-11-26
6
542 Views
Last Modified: 2008-03-10
Experts,

I have a loan amort table.  The table is generated by a temporary query.  The temp qry has all the data such as expected pmt dates and amounts.  I am wanting to develop a report with all borrowers that have expected pmt dates coming soon.  
It would be like
Borrower1 - pmt date XXX
Borrower2 - pmt date XXX
...
BorrowerX - pmt date XXX

I wont be able to generate a rpt if the temp qry only keeps the data temporarily.  It does not save the data.  I have heard that saving the data is not a good idea but if I am wanting to develop a report with expected pmt dates then I would think it would be necessary to save the data.  

What do some of the experts think about this?
0
Comment
Question by:Petrobras
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 400 total points
ID: 18014671
Hi Petrobras,

When I develop reports that rely on complex and/or multi-stage calculations, I will sometimes write the data I
need in the report to a "temp" table, and base the report on the table contents.  (When I do it, it is not truly
a "temp" table--it is a permanent table, but I use it *only* to facilitate report calc, and the data do not stay
permanently.  The first step in running the report always clears out the results from the last run.  I use VBA
code to manage it all.)

If you go with that approach, you will want the "temp" table(s) to go in the *front end*, and not the back end--
that ensures, if you have two users running the report at once, that they will not interfere with each other's
calculations.

Regards,

Patrick
0
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 100 total points
ID: 18014684
Hello P,
Assuming you're creating a qryDef to produce the report data, you can assign it as the recordsource for the report. This is common practice. Something like so

Function OpenMyReport()
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim sTmpQuery As String
    'Name the temporary output query
    sTmpQuery = "TempQryName"
    'Create a Query Definition for the Temporary Query
    CurrentDb.QueryDefs.Delete sTmpQuery
    'Set the Query Definition to use the recordset
    Set qdf = CurrentDb.CreateQueryDef(sTmpQuery, "SELECT * FROM yourtemptable")
    'Open your report using "TempQryName" as your recordsource
    DoCmd.OpenReport "yourReport"
End Function

J
0
 

Author Comment

by:Petrobras
ID: 18014804
Patrick,

sorry so short but have to get running right this instance...

<When I do it, it is not truly a "temp" table--it is a permanent table, but I use it *only* to facilitate report calc, and the data do not stay permanently.
Ok the tbl might not be temp but the data inside it is correct?  So how could I develop a report that contains all data?  I would think I would have to dump from this tmp file?

Jeff: but the report would still be based off incomplete data?  

OK wait...I just checked the db and I think I do have a tbl with all the data.  I did not design the amort table so I dont know exactly how it works.  I will look more into it.  

I think it displays data for a cetain borrower then dumps it into a table.  Does that sound like the right way?


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 18016273
Petrobras,

Without knowing more about what your app and this report do, it's hard for me to advise you further.

Patrick
0
 

Author Comment

by:Petrobras
ID: 18016399
Patrick, I think I am OK.  Before posting the question I had thought that I did not have a tbl with ALL info much like the "dump" I was referring to.  Sorry.  I should have checked into it further.  

thank you for the help in letting me know that I do have it the way an expert would design.

P
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 18018712
Glad to help, P.

It's one of those areas where there is not one correct answer.  Different developers will take different
approaches here, I think.

Patrick
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Balance After Payment 12 62
Displaying images within Access Form 6 76
Why Won't My Excel Workbook Refresh The Data 2 47
Excel graph in access report 1 33
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question