Running an in and out stock movement query in order

[tw-maleklive.com]

Hello....
I am trying to create a query statement in access to show the current stock balance like a tree ,I use a subquery to get the current balance with this SQL statement :

SELECT TransactionDataTotalBalance.TransID, TransactionDataTotalBalance.TransDate, TransactionDataTotalBalance.RecQuantity, TransactionDataTotalBalance.SendQuantity, TransactionDataTotalBalance.Accountant, TransactionDataTotalBalance.Yarn, TransactionDataTotalBalance.StockName, (Select Sum([RecQuantity]-[SendQuantity])From TransactionDataQ Where TransactionDataQ.TransID<=TransactionDataTotalBalance.TransID And TransactionDataQ.Yarn=TransactionDataTotalBalance.Yarn) AS Balance
FROM TransactionDataQ AS TransactionDataTotalBalance
WHERE (((TransactionDataTotalBalance.Yarn)=26))
ORDER BY TransactionDataTotalBalance.TransID;

it show me the exact as I want to be if I use the TransID And TransDate with the same order as follow:

the balance is correct but,

if I Add or Edit a Record to a previse transdate and change the SQL Order to :
SELECT TransactionDataTotalBalance.TransID, TransactionDataTotalBalance.TransDate, TransactionDataTotalBalance.RecQuantity, TransactionDataTotalBalance.SendQuantity, TransactionDataTotalBalance.Accountant, TransactionDataTotalBalance.Yarn, TransactionDataTotalBalance.StockName, (Select Sum([RecQuantity]-[SendQuantity])From TransactionDataQ Where TransactionDataQ.TransID<=TransactionDataTotalBalance.TransID And TransactionDataQ.Yarn=TransactionDataTotalBalance.Yarn) AS Balance
FROM TransactionDataQ AS TransactionDataTotalBalance
WHERE (((TransactionDataTotalBalance.Yarn)=26))
ORDER BY TransactionDataTotalBalance.TransDate, TransactionDataTotalBalance.TransID;

it look like this:

The balance is too wrong????

Could I have a help from any freinds??

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum. https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/ We look forward to having you around here, learning stuff and having fun!

 

[Isaac]

Welcome to AWF! This is the Introduction forum, but a moderator may move your question to a Query forum, where you may find more help on Query issues/topics.

 

[theDBguy]

Hi. Welcome to AWF!

I have moved your topic out of the Introduction Forum and locked it, since you already posted a duplicate one in the Queries Forum.

Good luck!