Difference of values in the same column ms access sql (mdb)

I have a table which contains two column with values that are not unique, those values are generated automatically and I have no way to do anything about it, cannot edit the table, db nor make custom functions.

With that in mind I’ve solved this problem in sql server, but it contains some functions that does not exist in ms-access.

The columns are Volume and ComponentID, here is my code in sql:

with rows as (
                 select row_number() over (order by volume) as rownum, volume
                from   test where componentid = 'S3')
 
select top 10
       rowsMinusOne.volume, coalesce(rowsMinusOne.volume - rows.volume,0) as diff
from   rows as rowsMinusOne
                left outer join rows
                       on rows.rownum = rowsMinusOne.rownum - 1

Sample data:

58.29168 
70.57396
85.67902
97.04888
107.7026
108.2022
108.3975
108.5777
109
109.8944

Expected results:

Volume	diff
58.29168	0
70.57396	12.28228
85.67902	15.10506
97.04888	11.36986
107.7026	10.65368
108.2022	0.4996719
108.3975	0.1952896
108.5777	0.1801834
109	0.4223404
109.8944	0.89431

I have solved the part of the coalesce by replacing it with NZ, I have tryed to use the DCOUNT to solve the row_number (How to show the record number in a MS Access report table?) but I reveive the error that it cannot find the function (I am reading the data by code, that is the only thing I can do).

I also tryed this but, as the answer says I need a column with a unique value which I do not have nor can create Microsoft Access query to duplicate ROW_NUMBER

Answer

Consider:

SELECT TOP 10 Table1.ComponentID, 
DCount("*","Table1","ComponentID = 'S3' AND Volume<" & [Volume])+1 AS Seq, Table1.Volume, 
Nz(Table1.Volume -
    (SELECT Top 1 Dup.Volume FROM Table1 AS Dup 
     WHERE Dup.ComponentID = Table1.ComponentID AND Dup.Volume<Table1.Volume 
     ORDER BY Volume DESC),0) AS Diff
FROM Table1
WHERE (((Table1.ComponentID)="S3"))
ORDER BY Table1.Volume;

This will likely perform very slowly with large dataset.

Alternative solutions:

1. build query that calculates difference, use that query as source for a report, use textbox RunningSum property to calculate sequence number

2. VBA looping through recordset and saving results to a ‘temp’ table

3. export to Excel