Running an in and out stock movement query in order (1 Viewer)

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

June7

AWF VIP
Local time
Today, 01:07
Joined
Mar 9, 2014
Messages
5,425
It would be best to build a report and do calcs there.
 

plog

Banishment Pending
Local time
Today, 04:07
Joined
May 11, 2011
Messages
11,613
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

Immoderate Moderator
Staff member
Local time
Today, 04:07
Joined
Feb 28, 2001
Messages
27,001
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

Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 17:07
Joined
May 7, 2009
Messages
19,170
use TransactionDataQ and not TransactionDataTotalBalance Inside your sub-Query:
Code:
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

New member
Local time
Today, 12:07
Joined
Apr 1, 2021
Messages
8
use TransactionDataQ and not TransactionDataTotalBalance Inside your sub-Query:
Code:
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

Code:
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

New member
Local time
Today, 12:07
Joined
Apr 1, 2021
Messages
8
use TransactionDataQ and not TransactionDataTotalBalance Inside your sub-Query:
Code:
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 :

1617380460958.png




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

Could you solve this problem ?
 

June7

AWF VIP
Local time
Today, 01:07
Joined
Mar 9, 2014
Messages
5,425
Can attach file to post. Follow instructions at bottom of my post.
 

tw-maleklive.com

New member
Local time
Today, 12:07
Joined
Apr 1, 2021
Messages
8
Query name "TransactionDataBalanceQ"
 

Attachments

  • SiparisTakip_With Data.zip
    1.5 MB · Views: 244

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:07
Joined
May 7, 2009
Messages
19,170
see Query2.
 

Attachments

  • SiparisTakip_With Data.zip
    1.4 MB · Views: 256

Users who are viewing this thread

Top Bottom