<div>
Storing the balance in the record is incorrect and will cause data anomalies and extra processing. &nbsp;What happens if you enter a payment out of order? &nbsp;Every record after the new one will have an incorrect balance.<br />
<br />
Calculate the balance on the report using the RunningSum property.<br />
<br />
If you elect to store the balance in the record, be prepared to disallow back dates as input or do bulk updates to recalculate the balance going forward from the back date entry.
</div>


<div>
Pat, i would only want to create a calculated field. &nbsp;I think that means its not in the record. &nbsp;Maybe that changes the approach.
</div>


<div>
If you want the running sum in a report, the process is trivial and efficient. &nbsp;If you want the running sum in a query, the process is more difficult but the problem is that with larger recordsets, it is extremely expensive process wise.<br />
<br />
Is the detail going back to the beginning of time or is it done monthly, quarterly, annually or by some other grouping?<br />
<br />
If you are grouping, you might need the ability to calculate a starting balance and then the report or query will show the details of the current period. &nbsp;We really need more information to offer a reasonable solution.<br />
<br />
To calculate a beginning balance, create a query that selects the detail through some date say, March 31, 2017 if you are doing this quarterly. &nbsp; The query will sum the data to one record for each &quot;something&quot; such as customer, account, project, whatever.<br />
<br />
Then you would create a second query to select the detail records for the specified date which continuing the example would be April 1, 2017.<br />
<br />
The final query unions the two sets. &nbsp;You will need to come up with something in the first query that can be incorporated in the sort to ensure that the balance record of a set always sorts first.
</div>


<div>
Pat, I thought it would be a little more straight forward. Its a little out of my league as you have described. &nbsp;<br />
but to answer your question:<br />
&lt;Is the detail going back to the beginning of time or is it done monthly, quarterly, annually or by some other grouping?<br />
Yes, back to the beginning of time. <br />
<br />
I will keep it open a bit longer.
</div>


<div>
If it makes a difference, there would be a where condition: &nbsp;On ID.
</div>


<div>
If you don't need to do periodic reporting, then you don't need to calculate the beginning balance for a period. &nbsp;Just use the RunningSum property in a report. &nbsp;A property setting is all it takes. &nbsp;Just add the amount field twice to the report. &nbsp;One will show the individual amount, the other firld will have its RunningSum property set to Over All and Access will calculate the running sum for you. &nbsp;If your report has breaks for company, project, whatever, then the RunningSum property will need to be set to Over Group.
</div>


EE_Balance_4-26-17.accdb

<div>
wow. &nbsp;You probably did that is 5 min. &nbsp;...amazing. &nbsp;than you very much.
</div>


<div>
can you close this thread now.
</div>


<div>
so sorry. &nbsp;I thought I did. &nbsp;thanks for reminding &nbsp;me.
</div>


<div>
I think I will need to ask a follow up though.
</div>


<div>
if interested, here is the path:<br />
<a href="https://www.experts-exchange.com/questions/29019246/Balance-after-Repayment-2.html" rel="ugc">https://www.experts-exchange.com/questions/29019246/Balance-after-Repayment-2.html</a>
</div>


<div>
<div class="content wysiwyg-content">
Experts,<br />
<br />
How could I show a balance after the repayment? &nbsp;<br />
I have attached a sample paired down db. &nbsp;<br />
<br />
I have a test query. &nbsp;<br />
The formula is tblDraws_Details.Amount - tblRepayment.Amount<br />
the issue I have is that if there is &gt;1 repayment then the balance is not correct. &nbsp;<br />
<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w17/1158818/Untitled.jpg" target="_blank" title="screenshot (98 KB)"><img alt="screenshot" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2017/04_w17/800_1158818/Untitled.jpg" /></a><br />
Grateful for your help.<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w17/1158817/EE_Balance_4-26-17.accdb" target="_blank" class="file-inline" title="Balance (1.0 MB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>EE_Balance_4-26-17.accdb</a>
</div>
</div>


Untitled.jpg

Experts,

How could I show a balance after the repayment?  
I have attached a sample paired down db.  

I have a test query.  
The formula is tblDraws_Details.Amount - tblRepayment.Amount
the issue I have is that if there is >1 repayment then the balance is not correct.  



Grateful for your help.

Balance After Payment

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.