Solved

Loan Amortization and a tmp query

Posted on 2006-11-26
6
549 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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