Calculate the Difference Between Multiple Rows Stored in the Same Column 1

[BV874]

Hello,
I am trying to create a query on a payment history table that shows the time in between payments to locate missed payments. Each customer is required to make a payment each month. Each payment is stored in a row in the table [CHECK_REGISTER]. I need to group the query by [CUS_NUM], and find the difference in between each [TRAN_DATE] when they are sorted in ascending order. Some customers will have a few months of records while others have years of records. The [TRAN_DATE]s are not always on the same day each month. I am using Access 2003 SP3. Is there a way to do this? Any help would be greatly appreciated.

Thanks!

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BV874]

I tried AdjDate: DateAdd("m",1,[TRAN_DATE]) and then took the difference between TRAN_DATE and AdjDate but then realized it would not work because of payments on different days, missed payments, and I was going in the wrong direction. I have been unable to come up with a method since. Any thoughts that could point me in the right direction? Thanks again.

 

[BV874]

I just tried making a temp table of all transactions. Then a second temp table with the min TRAN_DATE grouped by customer. I then deleted the min transactions from the first temp table using a delete distinctrow query. I then linked the original table CHECK_REGISTER with the revised temp table by CUS_NUM to show the values side by side in ascending order and take the difference from each other. It gave me a large number of duplicates.

 

[PHV]

What is the schema of the relevant tables ?
What is the SQL code of your attempts ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BV874]

I think what I need to do is find a way to number my query results. (1,2,3,4..) so I can inner join them in each table along with CUS_NUM.

First Query:
SELECT CHECK_REGISTER.CUS_NUM, CHECK_REGISTER.TRAN_DATE INTO TEMP_MISSEDPAY01
FROM CHECK_REGISTER
ORDER BY CHECK_REGISTER.TRAN_DATE;

Second Query:
SELECT CHECK_REGISTER.CUS_NUM, Min(CHECK_REGISTER.TRAN_DATE) AS MinOfTRAN_DATE INTO TEMP_MISSEDPAY02MIN
FROM CHECK_REGISTER
GROUP BY CHECK_REGISTER.CUS_NUM;

3rd Query:
DELETE DISTINCTROW TEMP_MISSEDPAY01.CUS_NUM, TEMP_MISSEDPAY01.TRAN_DATE, TEMP_MISSEDPAY01.*
FROM TEMP_MISSEDPAY01 INNER JOIN TEMP_MISSEDPAY02MIN ON (TEMP_MISSEDPAY01.CUS_NUM = TEMP_MISSEDPAY02MIN.CUS_NUM) AND (TEMP_MISSEDPAY01.TRAN_DATE = TEMP_MISSEDPAY02MIN.MinOfTRAN_DATE);

4th Query:
SELECT CHECK_REGISTER.CUS_NUM, CHECK_REGISTER.TRAN_DATE, TEMP_MISSEDPAY01.TRAN_DATE AS 2ndTranDate, [TEMP_MISSEDPAY01].[TRAN_DATE]-[CHECK_REGISTER].[TRAN_DATE] AS Difference
FROM CHECK_REGISTER INNER JOIN TEMP_MISSEDPAY01 ON CHECK_REGISTER.CUS_NUM = TEMP_MISSEDPAY01.CUS_NUM
ORDER BY CHECK_REGISTER.CUS_NUM, CHECK_REGISTER.TRAN_DATE, TEMP_MISSEDPAY01.TRAN_DATE;

Thanks.

 

[Golom]

Try something like this

SELECT C.Cus_Num, C.Tran_Date, N.Tran_Date, 
       DateDiff("d", C.Tran_Date, N.Tran_Date) As DaysBetween

FROM Check_Register As C INNER JOIN Check_Register As N
      ON C.Cus_Num = N.Cus_Num

WHERE N.Tran_Date = (SELECT MIN(X.Tran_Date) FROM Check_Register As X
                     WHERE X.Cus_Num = C.Cust_Num 
                       AND N.Tran_Date > C.Tran_Date)

ORDER BY C.Cus_Num, C.Tran_Date

This just takes two instances of the Check_Register table called "C" and "N". "C" picks up a record for a customer and "N" picks the next payment after that one as determined by the sub-query in the WHERE clause. It then computes the difference between the two payments in days. You could change it to "m" (i.e. Months) but that may look like he missed a payment when, in fact, he was just a bit late and made the payment after month-end.

 

[BV874]

Golom,
I tried the code and it is returning no results. Thanks.

 

[Golom]

Sorry ... Typo

SELECT C.Cus_Num, C.Tran_Date, N.Tran_Date, 
       DateDiff("d", C.Tran_Date, N.Tran_Date) As DaysBetween

FROM Check_Register As C INNER JOIN Check_Register As N
      ON C.Cus_Num = N.Cus_Num

WHERE N.Tran_Date = (SELECT MIN(X.Tran_Date) FROM Check_Register As X
                     WHERE X.Cus_Num = C.Cust_Num 
                       AND [b][red]X[/red][/b].Tran_Date > C.Tran_Date)

ORDER BY C.Cus_Num, C.Tran_Date

 

[BV874]

That worked perfectly, Thanks so much!