input form field on a report - qoh

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

Guest

Hi All,

I have 2 forms one for receiving items and the other for issuing items.
I have calculated stock in qty and stock out qty and the QOH on the fly on
the form.

I want the QOH of each item in the end of the day as a report.
How to include qoh field in a report since this is not a database field.

my table details :
Stock_in table
Sin_item_no, stock_in_qty

Stock_out table
So_item_no, stock_out_qty

Product table,
Item_no, item_desc,uom

item_no is the primary key linking both the stock in and out tables.

I need a report that has

ITEM_ NO SUM(STOCK _IN_QTY) SUM(STOCK_OUT_QTY) QOH

Is this possible. thanks in advance.
 
Try this ---
SELECT Product.Item_no, Product.item_desc, Sum(Stock_out.stock_out_qty) AS
SumOfstock_out_qty, Sum(Stock_in.stock_in_qty) AS SumOfstock_in_qty,
Sum(NZ([stock_in_qty],0)-NZ([stock_out_qty],0)) AS QOH
FROM (Product LEFT JOIN Stock_out ON Product.Item_no = Stock_out.So_item_no)
LEFT JOIN Stock_in ON Product.Item_no = Stock_in.Sin_item_no
GROUP BY Product.Item_no, Product.item_desc;
 
Hi Karl,

Thanks a ton for your query. I am getting the QOH calculated in the query
but stock in and stock out values are getting doubled.


eg.

original data

stock in stock out QOH
300 110 190

500 200 300

when i run the query:

stock in stock out QOH

600 220 380

1000 400 600

stock in and stock out value is doubled.
any way to work around this problem.
thanks
KARL DEWEY said:
Try this ---
SELECT Product.Item_no, Product.item_desc, Sum(Stock_out.stock_out_qty) AS
SumOfstock_out_qty, Sum(Stock_in.stock_in_qty) AS SumOfstock_in_qty,
Sum(NZ([stock_in_qty],0)-NZ([stock_out_qty],0)) AS QOH
FROM (Product LEFT JOIN Stock_out ON Product.Item_no = Stock_out.So_item_no)
LEFT JOIN Stock_in ON Product.Item_no = Stock_in.Sin_item_no
GROUP BY Product.Item_no, Product.item_desc;

--
KARL DEWEY
Build a little - Test a little


vandy said:
Hi All,

I have 2 forms one for receiving items and the other for issuing items.
I have calculated stock in qty and stock out qty and the QOH on the fly on
the form.

I want the QOH of each item in the end of the day as a report.
How to include qoh field in a report since this is not a database field.

my table details :
Stock_in table
Sin_item_no, stock_in_qty

Stock_out table
So_item_no, stock_out_qty

Product table,
Item_no, item_desc,uom

item_no is the primary key linking both the stock in and out tables.

I need a report that has

ITEM_ NO SUM(STOCK _IN_QTY) SUM(STOCK_OUT_QTY) QOH

Is this possible. thanks in advance.
Click to expand...
Click to expand...
 
Hi karl,
A correction in my results:


original data

stock in stock out
300 110

500 110

550 80

750 0

130 300


when i run the query:

stock in stock out

600 220

1000 110

600 200

1100 80

750 0

390 150

so it randomly doubles and retains the original stock out value for every 2
transactions i think.

Any helpful pointers.
 
Try just a smaller part of the query --
SELECT Product.Item_no, Product.item_desc, Sum(Stock_out.stock_out_qty) AS
SumOfstock_out_qty
FROM Product LEFT JOIN Stock_out ON Product.Item_no = Stock_out.So_item_no
GROUP BY Product.Item_no, Product.item_desc;

You really should have only two tables - product & Stock_Move.
Stock_Move ---
MoveID - autonumber - primary key
Item_no - foreign key
MoveDate - date
QTY - integer
Trans - text - In, Out, InvAdj
Invoice -

--
KARL DEWEY
Build a little - Test a little


vandy said:
Hi Karl,

Thanks a ton for your query. I am getting the QOH calculated in the query
but stock in and stock out values are getting doubled.


eg.

original data

stock in stock out QOH
300 110 190

500 200 300

when i run the query:

stock in stock out QOH

600 220 380

1000 400 600

stock in and stock out value is doubled.
any way to work around this problem.
thanks
KARL DEWEY said:
Try this ---
SELECT Product.Item_no, Product.item_desc, Sum(Stock_out.stock_out_qty) AS
SumOfstock_out_qty, Sum(Stock_in.stock_in_qty) AS SumOfstock_in_qty,
Sum(NZ([stock_in_qty],0)-NZ([stock_out_qty],0)) AS QOH
FROM (Product LEFT JOIN Stock_out ON Product.Item_no = Stock_out.So_item_no)
LEFT JOIN Stock_in ON Product.Item_no = Stock_in.Sin_item_no
GROUP BY Product.Item_no, Product.item_desc;

--
KARL DEWEY
Build a little - Test a little


vandy said:
Hi All,

I have 2 forms one for receiving items and the other for issuing items.
I have calculated stock in qty and stock out qty and the QOH on the fly on
the form.

I want the QOH of each item in the end of the day as a report.
How to include qoh field in a report since this is not a database field.

my table details :
Stock_in table
Sin_item_no, stock_in_qty

Stock_out table
So_item_no, stock_out_qty

Product table,
Item_no, item_desc,uom

item_no is the primary key linking both the stock in and out tables.

I need a report that has

ITEM_ NO SUM(STOCK _IN_QTY) SUM(STOCK_OUT_QTY) QOH

Is this possible. thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Hi Karl,

Thanks , but I have designed the database to receive data from 2 seperate
screens. Receiving and shipping are 2 separate activities . You are right i
must have just had one transaction for shipping and receiving but now is
there any way out. Can a virutal table be created to get the qoh values and
displayed.
thanks

KARL DEWEY said:
Try just a smaller part of the query --
SELECT Product.Item_no, Product.item_desc, Sum(Stock_out.stock_out_qty) AS
SumOfstock_out_qty
FROM Product LEFT JOIN Stock_out ON Product.Item_no = Stock_out.So_item_no
GROUP BY Product.Item_no, Product.item_desc;

You really should have only two tables - product & Stock_Move.
Stock_Move ---
MoveID - autonumber - primary key
Item_no - foreign key
MoveDate - date
QTY - integer
Trans - text - In, Out, InvAdj
Invoice -

--
KARL DEWEY
Build a little - Test a little


vandy said:
Hi Karl,

Thanks a ton for your query. I am getting the QOH calculated in the query
but stock in and stock out values are getting doubled.


eg.

original data

stock in stock out QOH
300 110 190

500 200 300

when i run the query:

stock in stock out QOH

600 220 380

1000 400 600

stock in and stock out value is doubled.
any way to work around this problem.
thanks
KARL DEWEY said:
Try this ---
SELECT Product.Item_no, Product.item_desc, Sum(Stock_out.stock_out_qty) AS
SumOfstock_out_qty, Sum(Stock_in.stock_in_qty) AS SumOfstock_in_qty,
Sum(NZ([stock_in_qty],0)-NZ([stock_out_qty],0)) AS QOH
FROM (Product LEFT JOIN Stock_out ON Product.Item_no = Stock_out.So_item_no)
LEFT JOIN Stock_in ON Product.Item_no = Stock_in.Sin_item_no
GROUP BY Product.Item_no, Product.item_desc;

--
KARL DEWEY
Build a little - Test a little


:

Hi All,

I have 2 forms one for receiving items and the other for issuing items.
I have calculated stock in qty and stock out qty and the QOH on the fly on
the form.

I want the QOH of each item in the end of the day as a report.
How to include qoh field in a report since this is not a database field.

my table details :
Stock_in table
Sin_item_no, stock_in_qty

Stock_out table
So_item_no, stock_out_qty

Product table,
Item_no, item_desc,uom

item_no is the primary key linking both the stock in and out tables.

I need a report that has

ITEM_ NO SUM(STOCK _IN_QTY) SUM(STOCK_OUT_QTY) QOH

Is this possible. thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top