Ms Access advanced calculation query, stock level? :S

[student2]

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!

[ChipR]

Your database design is making this difficult for you. To track stock correctly, you should have the items in one table and the sales transactions in another, or split into two. Consider something like this:

tblStockDetails
StockId - Long (pk)
OriginalStock - Long

tblSalesTransaction
StockId - Long (pk) (fk)
SaleDate - Date (pk)
AmountSold - Long
Price(optional)

tblShipment
ShipmentNumber - Long (pk)
StockID - Long (fk)
AmtReceived - Long

Then you have a totals query for each table grouped by StockID, join those with the tblStockDetails and you get your current onhand quantities easily.

[student2]

Thank a lot ChipR!

I changed the design of my DB to what you've suggested and it works well.
I've created the totals queries for the individual tables and that works well also.

However, I don't think I understand the 'join' aspect of it, because when I try to do it, I get the values duplicating for each StockId. (e.g. if I've received 10 for StockId 1 then I'm seeing that 10 for all StockIds)

[ChipR]

Great!
Just make sure that you have an Inner Join by having a line between the StockID in each table in your query. Or in the SQL syntax it should say INNER JOIN on ...

[student2]

Thank you very much ChipR!
Appreciate.