calculating qoh query

[Guest]

Hi ,

I have a scenario where i have to calculate items that have been issued in
and out of the system.

I have created a product table with product details .

stock_in_table - sin_itemno foreign key linking the product table
stock_out_ table- so_itemno foreign key linking the product table

My database structure

Products:

Product_Id
Item_No - primary key
Item_Des
Po_No

Stock_in_table:
ID
Sin_Item_no- foreign key
Line_no
Stock_in_qty


Stock_out_table:
ID
So_item_no- foreign key
Line_no
Stock_out_qty



I have 2 forms 1 for shipping in and another for shipping out

The form has the product details and the subform adds up all shipped in qtys.

the same is for items shipped out.

I have 2 seperate queries for stock_in & stock_out total per product.

Can you help me with a query which will help me calculate QOH and display it
in the form.

thanks in advance

 

[Guest]

You could look up the values directly from the tables by means of the DSum
function in an expression as the ControlSource of a text box in the parent
form:

=DSum("Stock_in_qty","Stock_in_table","Sin_Item_no = " &
[Item_no])-DSum("Stock_out_qty","Stock_out_table","So_Item_no = " & [Item_no])

This assumes that the key columns are a number data type. If they are text
data type then the values should be wrapped in quotes:

=DSum("Stock_in_qty","Stock_in_table","Sin_Item_no = """ & [Item_no] &
"""")-DSum("Stock_out_qty","Stock_out_table","So_Item_no = """ & [Item_no] &
"""")

NB the above expressions should be entered as a single line as the
ControlSource property; they will probably have wrapped over two or more
lines in your news-reader.

Ken Sheridan
Stafford, England

 

[Guest]

Hi Ken,

Thanks a ton. My QOH is getting calculated for both the receving and
shipping screen. Thanks a ton. you saved my day.

You could look up the values directly from the tables by means of the DSum
function in an expression as the ControlSource of a text box in the parent
form:

=DSum("Stock_in_qty","Stock_in_table","Sin_Item_no = " &
[Item_no])-DSum("Stock_out_qty","Stock_out_table","So_Item_no = " & [Item_no])

This assumes that the key columns are a number data type. If they are text
data type then the values should be wrapped in quotes:

=DSum("Stock_in_qty","Stock_in_table","Sin_Item_no = """ & [Item_no] &
"""")-DSum("Stock_out_qty","Stock_out_table","So_Item_no = """ & [Item_no] &
"""")

NB the above expressions should be entered as a single line as the
ControlSource property; they will probably have wrapped over two or more
lines in your news-reader.

Ken Sheridan
Stafford, England

Hi ,

I have a scenario where i have to calculate items that have been issued in
and out of the system.

I have created a product table with product details .

stock_in_table - sin_itemno foreign key linking the product table
stock_out_ table- so_itemno foreign key linking the product table

My database structure

Products:

Product_Id
Item_No - primary key
Item_Des
Po_No

Stock_in_table:
ID
Sin_Item_no- foreign key
Line_no
Stock_in_qty


Stock_out_table:
ID
So_item_no- foreign key
Line_no
Stock_out_qty



I have 2 forms 1 for shipping in and another for shipping out

The form has the product details and the subform adds up all shipped in qtys.

the same is for items shipped out.

I have 2 seperate queries for stock_in & stock_out total per product.

Can you help me with a query which will help me calculate QOH and display it
in the form.

thanks in advance