title: Having difficulty in getting previous query record
body: <p>I have 4 store at different levels. (Store A, Store B, Store C, Store D). The stores keep the items. (Item1, Item2, Item3, Item4 and Item5). </p>
  
  <p>I have 3 tables (Item Table, Store Table, 
  <a href="http://i.stack.imgur.com/Swl3N.png" rel="nofollow">Tracking Table</a>) and 1 query (
  <a href="http://i.stack.imgur.com/L6xwP.png" rel="nofollow">Store_rec query for Item2 in Store B</a>).</p>
  
  <p>Tracking table has columns (ID, T_Date, Item_ID, Item_Name, Store_From, Store_To, Amount, Remark).</p>
  
  <p>Store_rec query table is to check the changes of an Item in a Store (in my case - Item 2 in Store B). It has 5 columns (SrNo, T_Date, Location_To/From (From B to others/ From others to B), Received (B balance increase) and Dispatched (B balance decrease).</p>
  
  <p>My problems are:</p>
  
  <ol>
  <li><p>I can't sort SrNo in ascending order without affecting the row number of T_Date.</p></li>
  <li><p>I want to add a balance column in Store_rec query as shown in the picture. (Balance = Previous balance + Received - Dispatched).</p></li>
  </ol>
  
  <p>I searched in Google, read in forums, tutorials and examples but still can't solve my problem.</p>
  
  <p>Please help.</p>
  
  <pre><code>SELECT (
          SELECT COUNT(aa.[ID]) + 1
          FROM Tracking AS aa
          WHERE (
                  t.[Store_From] = "Store B"
                  OR t.[Store_To] = "Store B"
                  )
              AND aa.[ID] &lt; t.[ID]
          ) AS SrNo
      ,t.T_Date
      ,IIf(t.[Store_To] = "Store B", t.[Store_From], t.[Store_To]) AS [Location_To/From]
      ,IIf(t.[Store_To] = "Store B", t.[Amount], 0) AS Received
      ,IIf(t.[Store_From] = "Store B", t.[Amount], 0) AS Dispatched
  FROM Tracking AS t
  WHERE (
          t.[Store_From] = "Store B"
          OR t.[Store_To] = "Store B"
          )
      AND t.[Item_Name] = "Item 2"
  ORDER BY t.[T_Date];
  </code></pre>