Reg Access Query

Question

Look at the example below

---

type qty total A 10 10 A 5 15 S 7 8 A 12 20 S 5 15

---

I want an access query which can find total ie if type is 'A' add it to total and if type is 'S' subtract from total.Can anyone help me.

Answer 1

You could use the Iif function to invert rows of type S, such as:

SELECT Sum(IIf([type]="S",-[total],[total])) AS subtotal
FROM table;

Alternatively in the query view, you can do this by adding a new field as:

subtotal: IIf([type]="S",-[total],[total])

Then activating the Totals icon in the toolbar, and choosing Sum as the value for the Totals.

Answer 2

Something like this:

SELECT T1.type, T1.qty, T1.total, T1.total + T1.qty AS subtotal
  FROM MyTable AS T1
 WHERE T1.type = 'A'
UNION ALL
SELECT T1.type, T1.qty, T1.total, T1.total - T1.qty AS subtotal
  FROM MyTable AS T1
 WHERE T1.type = 'S';

or possibly this:

SELECT T1.type, T1.qty, T1.total, 
       T1.total + SWITCH(
                         T1.type = 'A', T1.qty, 
                         T1.type = 'S', 0 - T1.qty,
                         TRUE, NULL
                        ) AS subtotal
  FROM MyTable AS T1;

based on what you find easier to read, how you want to handle rows where is neither 'A' nor 'S', and which one performs best (in that order, IMO).