?
Solved

Loan Amortization and a tmp query

Posted on 2006-11-26
6
Medium Priority
?
551 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 93

Accepted Solution

by:
Patrick Matthews earned 1600 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 400 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 93

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 93

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 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