title: Difference of values in the same column ms access sql (mdb)
body: <p>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.</p>
  <p>With that in mind I've solved this problem in sql server, but it contains some functions that does not exist in ms-access.</p>
  <p>The columns are Volume and ComponentID, here is my code in sql:</p>
  <pre><code>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
  </code></pre>
  <p>Sample data:</p>
  <pre><code>58.29168 
  70.57396
  85.67902
  97.04888
  107.7026
  108.2022
  108.3975
  108.5777
  109
  109.8944
  </code></pre>
  <p>Expected results:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Volume</th>
  <th>diff</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>58.29168</td>
  <td>0</td>
  </tr>
  <tr>
  <td>70.57396</td>
  <td>12.28228</td>
  </tr>
  <tr>
  <td>85.67902</td>
  <td>15.10506</td>
  </tr>
  <tr>
  <td>97.04888</td>
  <td>11.36986</td>
  </tr>
  <tr>
  <td>107.7026</td>
  <td>10.65368</td>
  </tr>
  <tr>
  <td>108.2022</td>
  <td>0.4996719</td>
  </tr>
  <tr>
  <td>108.3975</td>
  <td>0.1952896</td>
  </tr>
  <tr>
  <td>108.5777</td>
  <td>0.1801834</td>
  </tr>
  <tr>
  <td>109</td>
  <td>0.4223404</td>
  </tr>
  <tr>
  <td>109.8944</td>
  <td>0.89431</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>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 (<a href="https://stackoverflow.com/questions/28083240/how-to-show-the-record-number-in-a-ms-access-report-table/28086340#28086340">How to show the record number in a MS Access report table?</a>) 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).</p>
  <p>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 <a href="https://stackoverflow.com/questions/39174944/microsoft-access-query-to-duplicate-row-number">Microsoft Access query to duplicate ROW_NUMBER</a></p>