inventory management problem

I have two tables:

purchase (ItemId, PurchQuantity, PurchaseDate); sales (ItemId, SalesQuantity, SalesDate);

With these two table could I able to find:

1. Quantity in Stock at any given date of any item.
2. Purchase and Sales and their OpeningStock and ClosingStock Report Ordered by date

If i could not do it, can I maintain a table like this

StockLevel (ItemId, OpeningStock, ClosingStock, AdjustedQuantity, TransactionType);

where transaction type = purchase/sales.

1. the problem i'm facing with this table is i want to update this table each and every time the purchase or sales table is updated.
2. purchase or sales transaction can be inserted, deleted or updated for any date, this makes this table more complex. forex:

please give me any suggestions or better solutions,

Is there any smart way to handle these kinds of transactions.

thanks in advance Received on Sat Oct 14 2006 - 07:27:36 CDT