sum in two tables to calculate QOH

[Guest]

The following is the scenario

stock _ in table

Id item_no pcs
1 1 10

2 2 30

3 1 22



stock_out_table

Id item_no pcs
1 1 6

2 2 12

3 1 6



So the Query result should show or the Qty on hand should show this result.

ItemNum QOH

1 20

2 1


can you help me with an apt. query. I tried using
select t1.itemnum,sum(t1.pcs-t2.pcs)

from t1,t2 where t1.itemnum=t2.itemnum

group by t1.itemnum
but it did not work. any suggestions .

tHANKS IN ADVANCE.

 

[Cindy]

Not sure what kind of performance hit this would be -
but you could create two queries that first provide the total in and
total out.
Bring in the item number and the pcs, make it a totals query with the
group by the item no and a sum of the pcs.

Then create a third query - base it on the two total queries.

The SQL for that query would look something like this (assuming you
named the objects the same):

SELECT qryStockIn.item_no, [qryStockin].[sumOfpcs]-nz([qryStockOut].
[Sumofpcs],0) AS OnHand
FROM qryStockIn LEFT JOIN qryStockOut ON qryStockIn.item_no =
qryStockOut.item_no;

I did a left join just in case you also need to show inventory for
pieces that came in but had no "outs".

Cindy

 

[Guest]

Hi Cindy,

Thanks for your help. But with this query i am accepting value of stock in
and stock out as parameters.

I need it to read values from the table.

is this possible.

thanks

Not sure what kind of performance hit this would be -
but you could create two queries that first provide the total in and
total out.
Bring in the item number and the pcs, make it a totals query with the
group by the item no and a sum of the pcs.

Then create a third query - base it on the two total queries.

The SQL for that query would look something like this (assuming you
named the objects the same):

SELECT qryStockIn.item_no, [qryStockin].[sumOfpcs]-nz([qryStockOut].
[Sumofpcs],0) AS OnHand
FROM qryStockIn LEFT JOIN qryStockOut ON qryStockIn.item_no =
qryStockOut.item_no;

I did a left join just in case you also need to show inventory for
pieces that came in but had no "outs".

Cindy

The following is the scenario

stock _ in table

Id item_no pcs
1 1 10

2 2 30

3 1 22

stock_out_table

Id item_no pcs
1 1 6

2 2 12

3 1 6

So the Query result should show or the Qty on hand should show this result.

ItemNum QOH

1 20

2 1

can you help me with an apt. query. I tried using
select t1.itemnum,sum(t1.pcs-t2.pcs)

from t1,t2 where t1.itemnum=t2.itemnum

group by t1.itemnum
but it did not work. any suggestions .

tHANKS IN ADVANCE.

 

[Michel Walsh]

SELECT item_no, SUM(qty)
FROM ( SELECT item_no, pcs AS qty FROM stock_in
UNION ALL
SELECT item_no, -pcs FROM stock_out)
GROUP BY item_no



should do. It would have been easier with just one table, doable since stock
out is a negative quantity, and stock in is a positive quantity. No need,
really, to have two tables.


Hoping it may help,
Vanderghast, Access VMP