quantity issues

[steve111]

hi,

ok , long story,

I have a table called stock, this table , has onorderqty,, pendingqty, stock qty,
at present these fields are filled in manually. how :

when a part is ordered someone alters the onorderqty to suit
when a part arrives we use the booking in form , the create GRN and qty recd this then says the parts are here but have not been inspected therefore move that quantiy from the onorderqty to the pending qty ,
when the field passed has yes, or short , move from pending to stockqty
if rejected or no is in the passed field remove the qty from the pending qty and put back onto the onorderqty

is this possible to do without manually entering it with formular in the stocklist form

thank
steve

 

[moto485]

I think you are just wanting to know if you can automate these processes rather than enter the data manually? if I understand then you will need to read up on some VBA code..

 

[steve111]

yes can we automate this,
to recap,

there is 4500 stock items appox, some at present may have values in the onorderqty, pending and stockqty.

when a purchase order is raised, the onorderqty will increase
when the part is booked in but not inspected it will have the qty booked in (could be different to the order qty, then this qty will go into the pending qty anf the onorderqty will reduce by that amount, when the inspector checks the parts if ok that quantity will be reduced from the pending qty to the stock qty, the if oart in rejected then the pending qty will reduce and the onorderqty will increase again, if the part has "no" in the passed filled it will stay in the pending qty until a decision is made to reject or axcept it

steve

 

[jdraw]

There can be a lot of complexity with inventory control. You may want to investigate / research some existing models and concepts.
FIFO, BackOrder, ReOrderPoint

http://www.databaseanswers.org/data_models/customers_and_inventory_mgt/index.htm

http://www.databaseanswers.org/data_models/inventory_and_sales/index.htm

http://allenbrowne.com/appinventory.html

 

[steve111]

hi ,

I think it can be done another way for the onorderqty and pending qty but I don't know how to do the formular,

if you look at the form status , you will see form called outstanding po subform this id all orders paced but not booked in yet , there fore this total could be sum qtyreqr,

the parts waiting inspection form , could also be sum qty received this would get me past the first 2 issues I have.

what I would like is 2 more fields in the stockist form that adds these 2 quanties.

the values are related by the stock number in the stocklist and the other 2 forms
I just don't know how to get these results from the above 2 forms into the stocklist form

thanks
steve
steve