Solved

Running Balance

Posted on 2014-12-16
12
189 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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
 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

791 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