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:

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

CodePudding user response：

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