On Sunday, November 22, 2020 at 6:40:57 PM UTC+3, Ron Weiner wrote:
rama has brought this to us :
On Sunday, November 22, 2020 at 12:20:01 AM UTC+3, Ron Weiner wrote:
rama brought next idea :
Hello
In my MS Access 2016 database table, I have four fields called ID, LoanID,
LoanDate, and LoanAmount. Now I am facing a problem to calculate number of
days between first loan and loan top-ups. Below shown is a typical example.
ID LoanID LoanDate LoanAmount 1 200 1/1/2020 $1000 2 200 07/1/2020 $500 >>> 10 200 11/1/2020 $500
I need to calculate number of days between initial loan date (1/1/2020) and
first top-up (7/1/2020). And first top-up (11/1/2020) and second top-up >>> (07/1/2020). How this can be done in Access. Is there any inbuilt function
for that. Currently there are so much data and not in a position to modify
the table in a better way. Kindly post your suggestions.
Appreciate your suggestions..
Thanks in advance.
Rama
I am not exactly sure what you are looking for, but since you did post
some Column Names and Sample Data, I thought I'd give it a shot. You
did not however give us the name of your table, so in the sql below I
refered to your table as TheTable. Here is the sql:
SELECT TT.ID, TT.LoanID, TT.LoanDate, TT.LoanAmount, (Select Top 1
LoanDate from TheTable where LoanID = 200 and LoanDate > tt.LoanDate
Order By id) AS NextLoanDate, DateDiff('d',[TT].[Loandate],(Select Top
1 LoanDate from TheTable where LoanID = 200 and LoanDate > tt.LoanDate
Order By id)) AS DaysBetween
FROM TheTable AS TT
WHERE (((TT.LoanID)=200))
ORDER BY TT.ID;
Here is the result I got with your Data:
ID LoanID LoanDate LoanAmount NextLoanDate DaysBetween
1 200 1/1/2020 $1,000.00 7/1/2020 182
2 200 7/1/2020 $500.00 11/1/2020 123
10 200 11/1/2020 $500.00
Rdub
--
This email has been checked for viruses by AVG.
https://www.avg.com
Thank you so much for the reply.
Basically, my database is very simple which records the customer loan information and calculate the cumulative interest (daily cumulative). The number of days is used to calculate the interest. The table name is TBL_LoanNTopup, and the field I did not mention in the post is AccInt (accumulated interest)
The posted code works exactly as requested. However, would you explain below
two points 1. How to change LoanID dynamically as it is used in NextLoanDate.
2. If next NextLoanDate is null can it be Date()
Kind regards
Rama
Glad that worked out. As to your first question you can parameterize
the query and provide a LoanID each time you call it. The answer to
your second question can vbe founbd in the Access NZ() function. Here
is the same query with your requested changes.
SELECT TT.ID, TT.LoanID, TT.LoanDate, TT.LoanAmount,
nz((Select Top 1 LoanDate from TheTable where LoanID =[TheLoanID] and LoanDate > tt.LoanDate Order By id),Date()) AS NextLoanDate, DateDiff('d',[TT].[Loandate],nz((Select Top 1 LoanDate from TheTable
where LoanID =[TheLoanID] and LoanDate > tt.LoanDate Order By
id),Date()) ) AS DaysBetween
FROM TheTable AS TT
WHERE (((TT.LoanID)=[TheLoanID]))
ORDER BY TT.ID;
In above example I have made the parameter name "TheLoanID". Here is
the output from the modified query.
ID LoanID LoanDate LoanAmount NextLoanDate DaysBetween
1 200 1/1/2020 $1,000.00 7/1/2020 182
2 200 7/1/2020 $500.00 11/1/2020 123
10 200 11/1/2020 $500.00 11/22/2020 21
Rdub
Hello,
Thank you so much for the great help and the query works fine.
I have added few more fields and introduced FV function to calculate the daily accumulated interest. Below posted is my current query and its results. Is there a way to automatically add LoanAmount with Interest to the TopUp amount. The data currently
shown in LoanAmout below is manually entered to calculate the accumulated interest.
example: Initial Loan = 2500, after 47 days the amount with 8% interest, it became 2524.8. When 1000 added it become 3525. Next interest for 3525 for 182 days calculated and added to the TopUp amount 500. If this can be done automatically, kindly help me.
Query:
SELECT TT.ID, TT.LoanID, TT.LoanDate, TT.LoanAmount, Nz((Select Top 1 LoanDate from TheTable where LoanID =[TheLoanID] and
LoanDate > tt.LoanDate Order By id),Date()) AS NextLoanDate, DateDiff('d',[TT].[Loandate],Nz((Select Top 1 LoanDate from TheTable
where LoanID =[TheLoanID] and LoanDate > tt.LoanDate Order By
id),Date())) AS DaysBetween, TT.Rate, TT.NPer, TT.TYPE, FV([Rate],[NPER]*[DaysBetween],0,[LoanAmount],[TYPE])/-1 AS CalculatedInterest, TT.TopUp
FROM TheTable AS TT
WHERE (((TT.LoanID)=[TheLoanID]))
ORDER BY TT.ID;
Results:
ID LoanID LoanDate NextLoanDate DaysBetween TopUp LoanAmount CalculatedInterest
328 200 11/15/2019 1/1/2020 47 0 2500 2524.89833390333
1000 200 1/1/2020 7/1/2020 182 1000 3525 3662.90147613328
1001 200 7/1/2020 11/1/2020 123 500 3663 3759.24198748259
1002 200 11/1/2020 11/23/2020 22 500 4772 4794.18755757902
Thank you so much for the great help once again
Kind Regards
Rama
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)