Retrieve previous row Record to subtract from current row above (water consumption)

[OHB]

Hello all Members of the Forum,
I built a query where I want to calculate the difference between two rows in the same column.
The previous Record is deducted from the next record just the row above and the difference between both value appear in the result of another column (same row).

My Access SQL formula is as follow :

SELECT t_compteur.Consomateur, t_compteur.Description, t_releve.Date_releve, t_releve.Releve_m3,
(SELECT MAX(Releve_m3) from t_releve AS Alias WHERE Alias.Releve_m3 < t_releve.Releve_m3 GROUP BY t_compteur.Description) AS PrevReleve_m3, t_releve.Releve_m3-PrevReleve_m3 AS Difference
FROM t_compteur RIGHT JOIN t_releve ON t_compteur.N_compteur = t_releve.N_compteur
ORDER BY t_compteur.Consomateur DESC , t_compteur.Description DESC , t_releve.Date_releve DESC;

The formula works well with some data but not at all with some other numbers…
What is wrong with the selection of data for picking wrong number?
I couldn’t identify the interference and I am wondering if I use the correct formula with the Max() function ?
Would there be another way to pass-by this bug or another function/formula to be used ?

Thanks for your support and I appreciated all your useful advices in this Forum.
Kind regards,
O.

 

[welshgasman]

Crossposted Retrieve previous row Record to subtract from current row above (water consumption)

 

[OHB]

Solved thanks to June7 @AccessForums with this solution : Microsoft Access tips: Subquery basics. Was missing the "Alias.ID" at the end
ORDER BY Dupe.ReadDate DESC, Dupe.ID)