?
Solved

Running Balance

Posted on 2014-12-16
12
Medium Priority
?
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 25

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 25

Accepted Solution

by:
chaau earned 2000 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 25

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 25

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 25

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

770 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