Date difference in ms access between row in report

Question

I have a table named CustomerLog with field following

ID    CustomerID      BuyingDate
1     1001            12/02/2020(dd/mm/yyyy)
2     2023            10/02/2020
3     2024            14/02/2020
4     1001            11/03/2020
5     2023            12/03/2020
6     2024            20/03/2020
7     1001            23/04/2020
8     2023            23/04/2020
9     2024            25/04/2020

Now I need a query like following

ID    CustomerID      BuyingDate      Difference
1     1001            12/02/2020      0
4     1001            11/03/2020      28
7     1001            23/04/2020      43

It will be very helpful if anyone can provide an example file for the cause I have read some post about this type but can't understand.

Answer 1

What you can do is to use a sub-query to get the previous date for the customer. You will also need to use the Nz (Null to Zero) function to set the first previous date for each customer to be the same as the buying date, and then use DateDiff to get the difference in days. Your SQL should look something like:

SELECT 
    C.ID, 
    C.CustomerID, 
    C.BuyingDate, 
    Nz((SELECT TOP 1 C1.BuyingDate FROM CustomerLog AS C1 WHERE C1.CustomerID=C.CustomerID AND C1.BuyingDate<C.BuyingDate ORDER BY C1.BuyingDate DESC),C.[BuyingDate]) AS PreviousDate, 
    DateDiff("d",PreviousDate,C.BuyingDate) AS Difference
FROM CustomerLog AS C;

Regards,