maintain stock from daily input-output table

[Ramnik]

hello all,
i have a production database in which i have different sections processing on the same product.
i have a daily production entry form on which datewise entry is done for each section.

i want to maintain the total stock of each section (sectionB) with productName, input from sectionA , production from sectionB, balance (input-production) . presently i used query for this ..
but i need a stock table in which entries should be inserted by an automatic query ....
how should i achieve this :
1. by an append query who runs every time to append the quantity when a daily entry is made .??
2. by an update query to replace the quantity with a qty from totals query ?
which one is best to use ??? and if any other solution to this plz tell ..
i.e. what is the best method to maintain inventory from input & output tables ??????? thanksssssss

 

[Ramnik]

hello ,
i just need to have a database design for the management of the total stock from the daily production table ....
i have a table named daily production sectionA with a composite key (productionId,date(pk),productId(pk),QtyPassed). and same goes for sectionB although i will add some fields to sectionB later //..
i want to know what is the method to create a stock table such as . productId(pk),qty(A),qty(B) ... is a query the best way but problems occurs .
and i need to perform entry only for the daily production and stock table is updated .... thankssssss

 

[Ramnik]

no replies !!!!!
is the question so dumb ? i cant find anywhere the good table design for a stock from daily production out of two sections .

 

[Galaxiom]

Developers will often avoid maintaining stock levels at all but calculate it with a query on the history of production quantities as required.

BTW Having tables for SectionA and SectionB does not sound like the data structure is optimised.