tw-maleklive.com
New member
- Local time
- Today, 12:07
- Joined
- Apr 1, 2021
- Messages
- 8
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:
View attachment 90507
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:
View attachment 90508
The balance is too wrong????
Could I have a help from any friends??
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:
View attachment 90507
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:
View attachment 90508
The balance is too wrong????
Could I have a help from any friends??