• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

Loan Amortization and a tmp query

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
Petrobras
Asked:
Petrobras
  • 3
  • 2
2 Solutions
 
Patrick MatthewsCommented:
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
 
jefftwilleyCommented:
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
 
PetrobrasAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Patrick MatthewsCommented:
Petrobras,

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

Patrick
0
 
PetrobrasAuthor Commented:
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
 
Patrick MatthewsCommented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now