GET DATA FROM TXT SUM GROUP BY AND SUBTRACT

Question

Trying to get data from txt file which contains buys and sells like in the format below. I want to group by items and subtract each other.

I made three queries like totalin and totalout and stock but when I subtract out from in, some items are missing which has not been sold.

This is the data table

+------+---------+--------+
| TYPE |  ITEM   | AMOUNT |
+------+---------+--------+
| BUY  | APPLE   |    100 |
| BUY  | ORANGE  |    100 |
| BUY  | APPLE   |    200 |
| BUY  | ORANGE  |    200 |
| SELL | APPLE   |     50 |
| SELL | APPLE   |     50 |
| SELL | ORANGE  |    100 |
| SELL | ORANGE  |    100 |
| BUY  | COCONUT |     50 |
| SELL | BANANE  |     30 |
+------+---------+--------+

I want this output

+---------+--------+
|  ITEM   | AMOUNT |
+---------+--------+
| APPLE   |    200 |
| BANANE  |    -30 |
| COCONUT |     50 |
| ORANGE  |    100 |
+---------+--------+

I made 3 queries for the result that i want, but unfortunately I am stuck.

Here is my queries

QUERY 1 TOTAL IN:
SELECT DATA.TYPE, DATA.ITEM, Sum(DATA.AMOUNT) AS TOTALIN FROM DATA GROUP BY DATA.TYPE, DATA.ITEM HAVING (((DATA.TYPE)="BUY"));

QUERY 2 TOTAL OUT:
SELECT DATA.TYPE, DATA.ITEM, Sum(DATA.AMOUNT) AS TOTALOUT FROM DATA GROUP BY DATA.TYPE, DATA.ITEM HAVING (((DATA.TYPE)="SELL"));

QUERY 3 STOCK:
SELECT DATA.ITEM, [BUY]![TOTAL_IN]-[SELL]![TOTAL_OUT] AS STOK FROM (DATA INNER JOIN BUY ON DATA.ITEM = BUY.ITEM) INNER JOIN SELL ON DATA.ITEM = SELL.ITEM GROUP BY DATA.ITEM, [BUY]![TOTAL_IN]-[SELL]![TOTAL_OUT];

How can I made a query which shows the rest as stock.

Many thanks.

Answer 1

You can sum in one go like:

SELECT ITEM, 
       Sum(iif(data.type = "BUY", AMOUNT, -AMOUNT)) AS Stock
    FROM DATA
    GROUP BY ITEM;