Re-allocating fields in a tables (appending)

[scottappleford]

Hi

Firstly let me thankyou for your help because I feel this may be a difficult question.

I have a stock control db that records transactions through purchase orders, which allocates each line item (product) in a purchase order to an employee and a project it is destined for. I have three tables, one for purchase orders, one for transactions and one for products.

I then have a seperate table for stock in and out with a two other look ups for projects and employees.

Now sometimes a product may become a stock item as it is in surplus and not used for the project.

My problem is how do I re-allocate the product as a stock item then when it is used allocated it to another project.

Basically we use an internal unique number for each purchase order and i have a query set up that will recall each product in a purchase order. The reason for this is to save time updating goods recieved when an order turns up as i call all the item in a purchase order in one go rather than going to each product one at a time to update.

I think the db may be to advanced in the set up and feel a restructure of the db is the only solution but hopefully i not. I am no means an advanced user buit have done ok so far in seeting this db up e.g. my knowledge of vba is limited etc.

This has only just come up in what is needed so it is an after thought, however is very important for the operation of the db as a stock control system.

All advice much appreciated in advance.

scott

 

[neileg]

I suggest you consider creating a new transaction type that will work the same way as your purchase order, but is an internal transfer that posts a minus value to your project and a plus value to your stock. Then when you use the stock item, create another transfer that posts a minus value to stock and a plus value to the new project.

An alternative would be to go back and alter the original purchase order to allocate the item to stock and then alter it again to allocate to the new project. I don't like this approach because there is no audit trail, but it would work.

PS This is not a trivial subject. Stock control is a complex area and you may end up wishing you'd never started!

 

[scottappleford]

further explanation

Thanks for your reply.

I now realise it is comples subject. And I am ok with db but not an expert by any means. I do not want to wish I did not start as it will really solve a problem here if i can getb this new feature to work.

Now I am going to ask that you walk me through your suggestion (if thats ok)?

It is alot to ask but I would be really grateful and any knowledge I gain from you I will certainly share as I would like to start to contributr to this forum rather than always asking the questions.

Would be a good idea if you do not mind - to post to you a copy of the db?

thanks

scott