Solved

Running Balance

Posted on 2014-12-16
12
182 Views
Last Modified: 2014-12-18
Experts,

I have an attached query (Q_RunningBalance).  
I have a dilemma.
I need the Q_RunningBalance to show, as the beginning balance, the previous ending balance.  

for example,
initial balance = 100
expense Nov = 20
Ending Balance Nov = 80
Begining Balance Dec = 80

I need to use the Ending Balance as the Begining Balance.
Much like how your checking account will show you a balance after each expense/deposit.  

How could the query (Q_RunningBalance) be modified to use the Ending Balance as the beginning balance for each Type (JBIC, NEXI, Uncovered)?

thank you
Database1Rev--1-.accdb
0
Comment
Question by:pdvsa
  • 7
  • 5
12 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40504026
Why do you think your query is a running balance query? Have a look at the figures on this picture. Each of them is the initial balance - expenses
running
0
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40504036
The correct query for both will be this:
SELECT Q_Disbursement.Type, Q_Disbursement.RequestedDisbDate, 
tblFacilityAmounts.FacilityAmount - NZ((SELECT sum(SumOfAmount) AS sumsum FROM Q_Disbursement a where a.Type = tblFacilityAmounts.Type and a.RequestedDisbDate < Q_Disbursement.RequestedDisbDate), 0) AS [Beginning Balance], 
Q_Disbursement.Amount, 
[FacilityAmount]-(SELECT sum(SumOfAmount) AS sumsum FROM Q_Disbursement a where a.Type = tblFacilityAmounts.Type and a.RequestedDisbDate <= Q_Disbursement.RequestedDisbDate) AS [Ending Balance]
FROM Q_Disbursement INNER JOIN tblFacilityAmounts ON Q_Disbursement.Type = tblFacilityAmounts.Type;

Open in new window

correct
0
 

Author Comment

by:pdvsa
ID: 40504055
Chauu, that looks fantastic.  I am not in front of my computer however that looks like the answer to me.  Thank you for the great help!
0
 

Author Closing Comment

by:pdvsa
ID: 40504058
I see you have a query inside of a query.  This
Is really complicated techy stuff.  Thank you once again.
0
 

Author Comment

by:pdvsa
ID: 40507255
Chauu,

Does the query account for a 0 (zero) disbursement?  
Please see the attached pic.
Let me know if there should be a tweak.


Ending Bal
0
 
LVL 24

Expert Comment

by:chaau
ID: 40507594
It is because of the duplicates. Why the dates are duplicated? I will check in a couple of hours
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:pdvsa
ID: 40507743
Yes, I do see the dates are the same in the query ("Expected Disbursement Date" field name is [RequestedDisbDate]).  I do have this situation.  Quite possibly it is an easy fix?  I will wait for you to be free.  

thank you
0
 

Author Comment

by:pdvsa
ID: 40507748
fyi: The beginning balance is accurate though even with the duplicate dates.
0
 
LVL 24

Expert Comment

by:chaau
ID: 40507900
Have you modified the query? I cannot see ExpectedDisbDate in the original query. How did you modify it? Can you send me an updated database with the duplicates?
0
 

Author Comment

by:pdvsa
ID: 40508027
I did just a little.  It shouldnt affect it though I believe.  

Attached is the updated with the duplicates on tblDisbursement_amounts
Database1Rev--2-.accdb
0
 
LVL 24

Expert Comment

by:chaau
ID: 40508049
I see, you have done "a little". You need to use DisbRQDate for the running balance:
SELECT
  Q_Disbursement.Type,
  Q_Disbursement.DisbRQDate,
  Q_Disbursement.DisbRecdDate,
  Q_Disbursement.RequestedDisbDate, 
  tblFacilityAmounts.FacilityAmount-Nz((SELECT sum(SumOfAmount) AS sumsum FROM Q_Disbursement a where a.Type = tblFacilityAmounts.Type and Nz(a.DisbRQDate, a.RequestedDisbDate) < Nz(Q_Disbursement.DisbRQDate, Q_Disbursement.RequestedDisbDate)),0) AS [Beginning Balance], 
  Q_Disbursement.Amount, 
  [FacilityAmount]-(SELECT sum(SumOfAmount) AS sumsum FROM Q_Disbursement a where a.Type = tblFacilityAmounts.Type and Nz(a.DisbRQDate, a.RequestedDisbDate) <= Nz(Q_Disbursement.DisbRQDate, Q_Disbursement.RequestedDisbDate)) AS [Ending Balance]
FROM Q_Disbursement 
  INNER JOIN tblFacilityAmounts ON Q_Disbursement.Type = tblFacilityAmounts.Type;

Open in new window

0
 

Author Comment

by:pdvsa
ID: 40508114
very nice.  You are too good.  I see you added Nz
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now