Need alternative to DLOOKUP

  • Thread starter Thread starter Stephen Lynch
  • Start date Start date
S

Stephen Lynch

I have a query that caculates the balance due on a loan. I have asked
everywhere and no one seems to know how to accomplish this.

Anyway I got it to work, but the DLOOKUP is so slow it is a waste of my
time. I need to have it because it refers to the prior balance to calculate
the interest and principal and then determine the new balance by deducting
the principal.

Here's my query.

SELECT
IIf([RecCounter]=1,[LoanAmount],DLookUp("[Query13]![EndBalance]","Query13","[RecCounter]
= " & [RecCounter]-1)) AS StartBalance,
([Days]*[StartBalance])*[IntRateFactor] AS Interest, [Loan]-[Interest] AS
Principal, [StartBalance]-[Principal] AS EndBalance, Loan1a.RecCounter
FROM Loan1a;


Does anyone have any ideas. I tryed elookup but that also is slow.

How about so code that rewrites each row. HELP!!!
 
When I have done this to create payment tables, I have used code to create a
temporary table. This seemed to be much faster.
 
Stephen said:
I have a query that caculates the balance due on a loan. I have asked
everywhere and no one seems to know how to accomplish this.

Anyway I got it to work, but the DLOOKUP is so slow it is a waste of my
time. I need to have it because it refers to the prior balance to calculate
the interest and principal and then determine the new balance by deducting
the principal.

Here's my query.

SELECT
IIf([RecCounter]=1,[LoanAmount],DLookUp("[Query13]![EndBalance]","Query13","[RecCounter]
= " & [RecCounter]-1)) AS StartBalance,
([Days]*[StartBalance])*[IntRateFactor] AS Interest, [Loan]-[Interest] AS
Principal, [StartBalance]-[Principal] AS EndBalance, Loan1a.RecCounter
FROM Loan1a;
Click to expand...


Seems like the first thing to do is add a WHERE clause to
select the Account. Make sure the Account field in indexed.

As you surmised, you can probably get the biggest
improvement from getting rid of the DLookup altogether. You
never said what Query13 accomplishes, so I'm not sure what
all of your fields are used for, but it looks like you might
be able to use a self join instead. This is just an idea
for you to play with, it is certainly not complete.

SELECT IIf(L.RecCounter=1, L.LoanAmount, P.StartBalance)
AS NewStartBalance,
(L.Days * L.StartBalance) * L.IntRateFactor AS Interest,
L.Loan - L.Interest AS Principal,
L.StartBalance - L.Principal AS EndBalance,
L.RecCounter
FROM Loan1a AS L LEFT JOIN Loan1a AS P
ON L.Account = P.Account
AND P.RecCounter = L.RecCounter - 1
WHERE L.Account = xxxxxx
 
Back
Top