Query for Progressive Balance Stock

[sazd1]

I have three tables:
PurchasesTable: PId, ItemId, PDate, ItemName, PQuantity, PPrice, PAmount.
SalesTable: SId, ItemId, SDate, ItemName, SQuantity, SPrice, SAmount.

I want to construct a query to make a StockTable: SDate, ItemId, ItemName, QuantityPurchased, QuantitySold, Balance.
The sample data of StockTable is attached in file herewith.

The problem I am having is with the Balance column. I want to calculate Balance as (Balance-(QuantityPurchased-QuantitySold)).
Please advise how I can fix this issue of Progressive Balance calculation of Stock.

 

[John Big Booty]

I would have a tables to store your stock details, stock in store and stock transactions. They'd look something like this;

TBL_ItemDtl
ItemID (PK)
ItemName
ItemPrice
etc.

TBL_StockInStore
ItemID (FK)
StockInStore (Number of units in store)

TBL_Transactions
TransID (PK)
TransDate (Transaction Date)
ItemID (FK)
UnitsMoved (number of units sold/purchased)
CustomerID (FK)

You can then write an update query that updates your that either add or subtracts your transactions from the StockInStore in your
TBL_StockInStore

 

[neileg]

I don't agree with John. Storing stock balances is usually a bad idea. There's plenty of discussion in these forums about this subject.

Szad, you need to get all of your transactions into one data set. If it was me, I would have all of the sales and purchases in one table, with sales quantities set to negative. A simple sum will then give you the stock balance. If you have no choice but to have two tables, then you can create a Union query to bring them together. Again, this is covered in these forums.

The running sum feature of a report is then the easiest way to create your balance figures.

 

[John Big Booty]

Neileg has a valid point, there is really no need for a Stock on hand table as this can be easily determined at any time, simply by summing all the transactions for any given product. Given that sales should be stored as negative values and inward stock movements as positive.