Stock Control DB Issues

  • Thread starter Thread starter David
  • Start date Start date
D

David

I would appreciate some help on how to approach the
design elements for a Stock Control Database that I am
working on using Access 2000.
Included in the data for each item is an Opening Balance,
Adjustment for this period and a New Closing Balance.
(These are all quantities)
I am unable to see a way to perform these calculations
without creating fields for each of them. And then how to
ensure that the amended data is only calculated as and
when required. The New Closing Balance is the only
necessary item to save.
There will be data for about 200 items and it would be
preferred to run the update calculation weekly. How
should this be done ensuring that only the items on which
the data was amended are updated?
Can calculations be performed on unbound text boxes as it
would be unnecessary to store these as most of the data
should be returned to Zero after calculation?.
Any pointers would be much appreciated.
 
Simple solution:

tblItems < tblTransactions

One table identifies each item for which counts are taken. The second table
consists of + beginning balance, + additions, - subtractions. Store the
transactions. Run a query to sum all transactions for each item to arrive
at the balance. Don't calculate and store a balance as this is a) not
necessary and b) bad practice.
 
Back
Top