Solved

Running Balance

Posted on 2014-12-16
12
185 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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.

930 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

16 Experts available now in Live!

Get 1:1 Help Now