I'm using Ms Access 2003 to create a DB for my school project and I need to accomplish the following:-
* Calculate balances for stock (inclusive of sales and replenishment to stock)
My table (StockDetails):
I designed a query that says:
SELECT StockDetails.StockId, StockDetails.OriginalStock, StockDetails.AmountSold, StockDetails.AmtReceived, [OriginalStock]-[AmountSold]+[AmountRcd] AS Balance
However, from this query I'm only able to have it function (calculate) correctly for the first entry, every other entry goes back to :-
*Calculate from the original (OriginalStock) amount e.g. if the OriginalStock is 15 and 1 item was sold then it would be 14 as the Balance BUT 15 still remains as the original stock. So the next time I try to conduct a sale of this item it will once again assume the 15 as the OriginalStock.
Thus I would like to have the Balance to be the same as the OriginalStock amount each time the level is altered either by sale or replenishment.
I've tried designing many queries but none have been successful to accomplish the desirable.
Any assistance rendered to achieve the result desired would be greatly appreciated.
Sorry about the late reply. Thanks for your response.
I have readjusted the table to now reflect 3 separate tables so I'm now able to accomplish this by using the Sumof.... function.
Many Thanks for your reply!