sum in two tables to calculate QOH

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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


Cindy said:
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.
Click to expand...
Click to expand...
 
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
 
Back
Top