Query to find the running sum of a column(qty) based on type(if 'a' add else subtract)

Question

I have table with data as follows:

Store     ItemNo     Type   Billno   Qty
London      1          A      1      10
London      1          A      2       5
London      1          S      1       7
London      1          A      3       5
London      1          S      2       7
London      2          A      1      19
London      2          S      2       5
London      2          A      3      11
Paris       1          A      1      15
Paris       1          S      2       8
Paris       1          A      3       9
Paris       2          A      1      10
Paris       2          S      2       5

Now i want to calculate TotalQty, such that totalqty of qty of an itemno under particular store is calculated based on type. i.e. if type is A, qty should be added to total and if it is S , subtracted from total as shown below. In the example below for store london ,itemno 1 row 3 is the last entry,so the totalQty gives the current quantity availble for that item in that particular store.

Store     ItemNo     Type   BillNo  Qty   TotalQty
London      1          A       1     10      10
London      1          A       2      5      15
London      1          S       1      7       8
London      1          A       3      5      13
London      1          S       2      7       6
London      2          A       1     19      19
London      2          A       2      5      24
London      2          S       3     11      13
Paris       1          A       1     15      15
Paris       1          S       2      8       7
Paris       1          A       3      9      16
Paris       2          A       1     10      10
Paris       2          S       2      5       5

Answer 1

Assuming I understand you right, you need to use the IIf statement:

SELECT Store, ItemNo, SUM(IIF(Type = 'A', Qty, 0 - Qty)) AS TotalQty
FROM MyTable
GROUP BY Store, ItemNo

should produce the following (untested):

Store    ItemNo    TotalQty
London     1          8
London     2         13
Paris      1         16
Paris      2          5

Answer 2

I think this is it (I was confused but then spotted your data is inconsistent between table and results):

SELECT S1.Store, S1.ItemNo, S1.Type, S1.Billno, S1.Qty, 
       (
        SELECT SUM(SWITCH(
                          S2.Type = 'A', S2.Qty, 
                          S2.Type = 'S', 0 - S2.Qty, 
                          TRUE, NULL
                          ))
          FROM StoreStuff AS S2
         WHERE S2.Store = S1.Store
               AND S2.ItemNo = S1.ItemNo
               AND S2.Billno <= S1.Billno
       ) AS TotalQty
  FROM StoreStuff AS S1
 ORDER
    BY S1.Store, S1.ItemNo, S1.Billno;