Solved

Running Balance

Posted on 2014-12-16
12
191 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
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: …

726 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