Re-allocating Fields In A Tables (appending)

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