Solved

Loan Amortization and a tmp query

Posted on 2006-11-26
6
533 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

815 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now