Q: How to build this query? i have table
http://oi58.tinypic.com/2s7xreo.jpg
 id    action_date  type  item_id  quantity
---    -----------  ----  -------  --------
 87    4/25/2014    1     s-1           100
 88    4/1/2014     1     s-1           150
 89    4/4/2014     1     s-1           200
 90    4/3/2014     1     s-2           222
 91    4/7/2014     1     s-2            10
 96    4/4/2014     1     s-2             8
 97    4/22/2014    1     s-2             8
 98    4/21/2014    2     s-1           255
 99    4/5/2014     2     s-1             6
100    4/6/2014     2     s-2           190
101    4/6/2014     2     s-3            96
102    4/8/2014     2     s-1           120
103    4/15/2014    2     s-2             3
104    4/16/2014    2     s-2             3

type column which mean if 1 this is in item to my shop >>> if 2 this is out item from my shop >>
i need query to give me result like this 
item   in   out   net 

s1     300  195  105

and so on >>
how to write query that give me this result >>
and if i must but them in tow table >> table for the in and table for the out if that help >>> how it build the query >
and thanx in advance :)
note :: i am work on access 

A: One way to get the result is to use an aggregate function on an expression that conditionally returns the value of quantity, based on the value of type.
SELECT t.item
     , SUM(CASE WHEN t.type = 1 THEN t.quantity ELSE 0 END) AS in_
     , SUM(CASE WHEN t.type = 2 THEN t.quantity ELSE 0 END) AS out_
     , SUM(CASE WHEN t.type = 1 THEN t.quantity ELSE 0 END) 
       - SUM(CASE WHEN t.type = 2 THEN t.quantity ELSE 0 END) AS net_
  FROM mytable t
 GROUP BY t.item

This approach works in Oracle, as well as MySQL and SQL Server.
(If you remove the SUM() aggregate function and the GROUP BY clause, you can see how that CASE expression is working. The query above gives the result you specified, this one is just a demonstration that helps "explain" how that query works.)
SELECT t.item
     , CASE WHEN t.type = 1 THEN t.quantity ELSE 0 END AS in_
     , CASE WHEN t.type = 2 THEN t.quantity ELSE 0 END AS out_
     , t.*
  FROM mytable t


UPDATE
Unfortunately, Microsoft Access doesn't support CASE expressions. But Access does have an iif function. The same approach should work, the syntax might be something like this:
SELECT t.item
     , SUM(iif(t.type = 1, t.quantity, 0) AS in_
     , SUM(iif(t.type = 2, t.quantity, 0) AS out_
     , SUM(iif(t.type = 1, t.quantity, 0)
       - SUM(iif(t.type = 2, t.quantity, 0) AS net_
  FROM mytable t
 GROUP BY t.item


A: SELECT item_id,
       In_col,
       Out_col,
       (In_col - Out_col) AS Net
FROM
  (SELECT item_id,
          sum(CASE WHEN TYPE = 1 THEN quantity END) AS In_col,
          sum(CASE WHEN TYPE = 2 THEN quantity END) AS Out_col
   FROM TABLE
   GROUP BY item_id) AS t1;

I hope this what you need.
