title: Calculating differences between two rows for access sql query
body: <p>I am trying to create a query with a new column containing differences between row n and row n+1 for column X from Table 1. </p>
  
  <hr>
  
  <p>Table 1</p>
  
  <p>Sample_ID: 1, 2, 3, 4, 5</p>
  
  <p>X: 42060, 42069, 42069, 42111, 42132</p>
  
  <hr>
  
  <p>Query 1 (ideal result)</p>
  
  <p>Sample_ID: 1, 2, 3, 4, 5</p>
  
  <p>Diff: 0, 9, 0, 42, 21</p>
  
  <hr>
  
  <p>I used the following sql. </p>
  
  <pre><code>SELECT [Table1].Sample_ID,[Table1].X - Prev.X AS Diff
  FROM [Table1] INNER JOIN [Table1] AS Prev
  ON [Table1].Sample_ID -1 = Prev.Sample_ID
  WHERE [Table1].Sample_ID &gt; 1
  </code></pre>
  
  <p>However, it omits the first row; thus, instead of getting a result presented above as Query 1, I get the following.</p>
  
  <hr>
  
  <p>Results</p>
  
  <p>Sample_ID: 2, 3, 4, 5</p>
  
  <p>Diff: 9, 0, 42, 21</p>
  
  <hr>
  
  <p>How can I retain the first row (Sample_ID=1) as 0 value?</p>