Hi :-)
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):
StockId (PK)
OriginalStock
AmountSold
AmtReceived
I designed a query that says:
SELECT StockDetails.StockId, StockDetails.OriginalStock, StockDetails.AmountSold, StockDetails.AmtReceived, [OriginalStock]-[AmountSold]+[AmountRcd] AS Balance
FROM StockDetails;
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. Some gave the response 'Circular reference........'
Any assistance rendered to achieve the result desired would be greatly appreciated.
Thanks a mil!