Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #14247

Re: Number of days between first and second record (same field)

Show all headers | View raw

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

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Number of days between first and second record (same field) rama <chanattu@gmail.com> - 2020-11-21 12:19 -0800
  Re: Number of days between first and second record (same field) Neil <neil@myplaceofwork.com> - 2020-11-21 15:41 -0500
  Re: Number of days between first and second record (same field) Ron Weiner <rw@domain.com> - 2020-11-21 16:19 -0500
    Re: Number of days between first and second record (same field) rama <chanattu@gmail.com> - 2020-11-22 05:21 -0800
      Re: Number of days between first and second record (same field) Ron Weiner <rw@domain.com> - 2020-11-22 10:40 -0500
        Re: Number of days between first and second record (same field) rama <chanattu@gmail.com> - 2020-11-23 08:56 -0800

csiph-web