UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Get Closing Balance Or Running Balance ?, Access 2013    
 
   
jainkamlesh
post Nov 22 2019, 07:16 AM
Post#1



Posts: 6
Joined: 22-November 19



Hello,
This is my first post ...
I want to have Closing balance on each day, for selected Purchase Order No (PO_NO)

Suppose i purchased 100 mt wide PO_No "5313" and i lifted the same as under :

3-11-2019 14.50 Mt
3-11-2019 15.50 Mt
4-11-2019 20.00 mt
5-11-2019 30.00 mt

Daily lifting to be totalled and deducted from purchase Qty.
Desired Result :

Lifting_Date Lift_Qty Balance :
3-11-2019 30.00 Mt 70.00 Mt ( **remarks: 100 mt - ( 14.50 + 15.50 ))
4-11-2019 20.00 mt 50.00 mt
5-11-2019 30.00 mt 20.00 mt

This result is shown in a List Box which gets updated upon "On Current " event . So when i click another PO_NO, releavant result shall appear.
Hope some one can help me please ...
Best Regards,



Attached File(s)
Attached File  2019_11_12.png ( 16.37K )Number of downloads: 1
 
Go to the top of the page
 
orange999
post Nov 22 2019, 07:36 AM
Post#2



Posts: 1,993
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


See this M$oft running total example.

--------------------
Good luck with your project!
Go to the top of the page
 
jainkamlesh
post Nov 23 2019, 06:52 AM
Post#3



Posts: 6
Joined: 22-November 19



Thanks. I had already gone through this. Tried all means like ID no etc. But when i post condition like Forms!Formsearch!PO_NO, it doesnt get refreshed.
I am using this qry in a list box.

Hope the attached SC will be helpful.Attached File  2019_11_16_LI.jpg ( 999.34K )Number of downloads: 7


So in short, i want to see either closing balance or running total of selected PO_NO, from subform/Split Form.
Help shall be apprecaited.
I want to have Closing balance on each day in the list box.
Regards,
Kamlesh Jain
This post has been edited by jainkamlesh: Nov 23 2019, 06:53 AM
Go to the top of the page
 
orange999
post Nov 23 2019, 10:07 AM
Post#4



Posts: 1,993
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I have mocked up your data to set up a query to get End of Day Balance

Id LiftDate LiftQty
1 03-Nov-19 14.5
2 03-Nov-19 15.5
3 04-Nov-19 20
4 05-Nov-19 30

CODE
SELECT tblLifts.LiftDate,100 as StartingQty
, Sum(tblLifts.Liftqty) AS SumOfLiftqty
, StartingQty - DSum("liftqty","tblLifts","Day(LiftDate )<=" & Day(liftdate)) AS EndOfDayBalance
FROM tblLifts
GROUP BY tblLifts.LiftDate;


CODE
LiftDate    StartingQty    SumOfLiftqty    EndOfDayBalance
03-Nov-19    100                    30       70
04-Nov-19    100                    20       50
05-Nov-19    100                    30       20



If you have a variable Starting quantity, then you could have a parameter query such as:

CODE
PARAMETERS [StartingQty] IEEEDouble;
SELECT tblLifts.LiftDate
, Sum(tblLifts.Liftqty) AS SumOfLiftqty
, StartingQty - DSum("liftqty","tblLifts","Day(LiftDate )<=" & Day(liftdate)) AS EndOfDayBalance
FROM tblLifts
GROUP BY tblLifts.LiftDate;

This post has been edited by orange999: Nov 23 2019, 10:50 AM

--------------------
Good luck with your project!
Go to the top of the page
 
jainkamlesh
post Nov 25 2019, 03:34 AM
Post#5



Posts: 6
Joined: 22-November 19



Hello Mr Orange smile.gif
Thanks for your kind reply.

The qry gives summary of entire table which contains more than 12000 records..

Now i wish to add one more condition that i want closing day summary of only selected PO_NO only.
PO_NO is text key in tbllifting.

I tried below code :

SELECT tblLifting.Lift_Date, Val([Forms]![FormSearchPurchase2]![Pur_Qty]) AS StartingQty, Sum(tblLifting.Lift_qty) AS SumOfLiftqty, [StartingQty]-DSum("lift_qty","tblLifting","Day(Lift_Date )<=" & Day([lift_date]) And "PO_NO = " & [Forms]![FormSearchPurchase2]![PO_No]) AS EndOfDayBalance, tblLifting.PO_no
FROM tblLifting
GROUP BY tblLifting.Lift_Date, tblLifting.PO_no
HAVING (((tblLifting.PO_no)=[Forms]![FormSearchPurchase2]![PO_No]));

It gives records for selected PO NO... but But it doesnt consider the PO_No clause for closing Qty.



Sorry to trouble you but i guess i am nearing to desired result and making some silly mistake in setting the qry.
Regards,


Attached File(s)
Attached File  2019_11_25__3_.png ( 109.51K )Number of downloads: 2
 
Go to the top of the page
 
gemmathehusky
post Nov 25 2019, 10:20 AM
Post#6


UtterAccess VIP
Posts: 4,745
Joined: 5-June 07
From: UK


The hard thing to do in an interactive situation (eg a query) is get a running total, as the running total changes depending on the sort order of the items
If you have a report, then there is a predetermined sort order, so there is no problem.

With a form, or a query, the easiest thing to do is total the transactions, and thereby get just an "outstanding balance"


--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
jainkamlesh
post Nov 28 2019, 02:12 AM
Post#7



Posts: 6
Joined: 22-November 19



Thank you sir.
Will surely try to dig it further ...
may be two seperate querries and than showing the results work..
Thanks again.
Go to the top of the page
 
projecttoday
post Nov 28 2019, 05:32 AM
Post#8


UtterAccess VIP
Posts: 11,245
Joined: 10-February 04
From: South Charleston, WV


Don't forget you can always use temporary tables. Make life easy.

--------------------
Robert Crouser
Go to the top of the page
 
jainkamlesh
post Nov 28 2019, 05:49 AM
Post#9



Posts: 6
Joined: 22-November 19



Thank you sir for your guidance.
But i am novice and never used this method..
Any sample/ demo or link shall be helpful.
Regards,
Go to the top of the page
 
projecttoday
post Nov 28 2019, 06:55 AM
Post#10


UtterAccess VIP
Posts: 11,245
Joined: 10-February 04
From: South Charleston, WV


You're still having trouble with this? I have not read through all the posts so I thought I would mention this. Temporary tables, also known as work tables, can be used to break data preparation down into steps. Instead of having to do everything at once, you can park data temporarily in a table. Then you can do something else to the data. And then, if necessary, something else. Or get more data if you need it. And combine the new data with the existing data. Or whatever you need. And so on, until the data are ready to present to the user.

The tables need not be temporary. The data are temporary. So don't be confused by the terminology. You just create a temporary table the same way you create any other table. The difference is that temporary tables are usually in the FE instead of the BE. If you prefer, you can create the table itself in code.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 07:42 AM