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:

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??

 

[June7]

It would be best to build a report and do calcs there.

 

[plog]

The error is a fundamental one--you don't have a valid way to sort your records.

Your query is doing it by ID--which means TransID=1 will be the first value in your balance. Chronologicaly though, by using TransDate, TransID=1 is tied for the second transaction (along with TransID=2,3,4) and TransID=8 is the first one.

The easiest way to do this would be with a timestamp of transactions not just a date. With that said, you can get to where you want with what you have. Your subquery--the one calculating the running sum--must take both TransDate and TransID into account to get the value you expect.

 

[The_Doc_Man]

OK, first thing to save yourself an incredible amount of headaches: Since your FROM clause for either query only names one source (despite the ALIAS), you don't need to qualify that source EXCEPT in the embedded subqueries. Which means less typing for you and easier for us to read. Therefore, your second query becomes

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

Now - as to why the balance differs between the two queries, you have a summation query as a sub-query and you are changing the order of record visitation (because the ORDER BY clauses differ). Different sort? Different contributors to the summation, so different result.

 

[arnelgp]

use TransactionDataQ and not TransactionDataTotalBalance Inside your sub-Query:

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

 

[tw-maleklive.com]

use TransactionDataQ and not TransactionDataTotalBalance Inside your sub-Query:

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

OK, first thing to save yourself an incredible amount of headaches: Since your FROM clause for either query only names one source (despite the ALIAS), you don't need to qualify that source EXCEPT in the embedded subqueries. Which means less typing for you and easier for us to read. Therefore, your second query becomes

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

Now - as to why the balance differs between the two queries, you have a summation query as a sub-query and you are changing the order of record visitation (because the ORDER BY clauses differ). Different sort? Different contributors to the summation, so different result.

You are right , thank you

 

[tw-maleklive.com]

use TransactionDataQ and not TransactionDataTotalBalance Inside your sub-Query:

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

Thank you for your replay,
I use your SQL Statement the result some how messing something bcz it show me :

as you see the first two record are positive so it must plus and the balance must be 190!!!

Could you solve this problem ?

 

[tw-maleklive.com]

Thank you for your replay,
I use your SQL Statement the result some how messing something bcz it show me :

View attachment 90528



as you see the first two record are positive so it must plus and the balance must be 190!!!

Could you solve this problem ?

If you have time i can send you the Database it will more easier for you??

 

[June7]

Can attach file to post. Follow instructions at bottom of my post.

 

[tw-maleklive.com]

Query name "TransactionDataBalanceQ"

 

[arnelgp]

see Query2.

 

[tw-maleklive.com]

Query name "TransactionDataBalanceQ"

see Query2.

I checked the Query is good ,thank you but the sort is not correct if i filter yarn and the yarn have transaction with different dates,so i will try to create an open book for every record to get right sort , thank you my friend for you help.