input form field on a report - qoh

V

vandy

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

KARL DEWEY

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;
 
V

vandy

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

vandy

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

KARL DEWEY

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

vandy

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

calculating qoh query 2
sum in two tables to calculate QOH 3
Problem locking a transaction record 5
database design issues- tracking item movement 0
HELP WITH DESIGN BASED ON MOVEMENT OF ITEMS 1
Passing forms values to a report 1
UPDATE QUERY HELP REQUIRED 4
Suppress printing group footer using Calculated field(s) 1

Top