Manufacture Parts to Unit.

[honey2wood]

Hi to everyone,
I am building an inventory database for parts that go into producing a product.
I have a parts table, goods in table and a goods out table. I also have a 'Transaction query' that takes goods in and subtracts goods out to produce a stock level.
This is all very basic and works ok.
Each of the products is made up of up to 20 parts. At the moment as I sell a product I use the goods out table and take one of each part off to leave the stock level balance. Again this works OK. BUT it takes ages to do this for each part everytime I sell a product.
What I want is a way to enter the Product name with the number sold that automatically reduces all the parts totals.
I have 8 different products each using some of the same and some individual parts which complicates it a bit. Reject parts can be dealt with individually.
I expect this has been asked for many times before but I have been trying to find a thread but not sure what to search for.
Any information will be gratefully received.

Graham

 

[Galaxiom]

Firstly consider changing the GoodsIn and GoodsOut tables to one table with positive and negative quantities. It would be faster to Sum this field than do a joined query on the in and out table.

However the completed product really should have a component table that lists how many of each part is in each completed item. Then the movement of the finished item can be multiplied by the component table quantities to show haw many parts were shifted without actually transacting the individual parts.

 

[honey2wood]

Hi Galaxiom
Thanks for getting back.
I have already combined the goosIn and goodsout in the same table.
I think I understand what you are saying. If I say I have sold 20 products I can say that I have used 20 of each of the parts. Does that mean that I set up a table that has 1 cell per part that always equals the sales. This total is then taken from the 'goods in' to give a stock level figure.
I am not quite sure how I lay that out though.

Graham