Report that shows credit balance

[Leesha]

Hi,
I give up on this one. I've attached a stripped down version of a database. There is one report in it that compares the invoice with the amount paid and shows the difference for each line item. This is working OK. I need it to show a running credit/balance after each line item, with the total credit balance at the end of each year and at the end of the report. The Total credit balance in the footers appears to be working but no matter how I set up additional headers and footers I can't get the credit balance after each line item. I hope this makes sense.
For example 9/1/18 has a payment of $4503.28 however the invoice amount for that month is $2027.62. This leaves a credit of ($2475.66) The reality of it is that there should not be a credit due since the extra payment in 9/1/18 paid for the invoice not paid in 8/1/18.
How do I set this up to either show a running total in the "Difference" column or have a separate line after each invoice/payment line that shows the difference or a zero if no difference?
Thanks,
Leesha

[HansV]

Does the attached version do what you want? I set the Running Sum property of the Difference text box to 'Over Group'. You can experiment by setting it to 'Over All'.

CreditBalanceTally.zip

[Leesha]

OMG Hans, you would shake your head if you saw all the things I tried and how many hours I tried to get this to work. I've never used the Running Sum Property before (obviously). This is so awesome!! Thank you!

Another question, before I spend forever trying to do this. The user wants me to set up an aging report and they want it broken out to show what is current, 30, 60, 90, 120 and over 120 days out. Do you have any suggestions as to how you would set this up, or properties I don't know about in Access?

Thanks so much!
Leesha

[HansV]

On which field do you want to do that? BillingPeriodStart, or InvoiceStartDate, or ...?

[Leesha]

Hi Hans,
BillingPeriodStartDate and InvoiceStartDate are the same. It's always the first day of the month. So either of those fields is fine. I'm so excited to see how this will look!
Leesha

[HansV]

See the attached version. I added a field named Aging to the report query and created a new report based on the same query, but grouped by the Aging field.

CreditBalanceTally.zip

[Leesha]

Thank you so much Hans! This is wonderful. I had worked on an aging report for this group quite awhile ago but couldn't really get it to be accurate. I'm tickled with this!
Thanks again for all of the new learning opportunities,
Leesha

[Leesha]

Back again. You know how I love to understand the formula. When you have a minute can you explain the rationale behind this to me.

Aging: Partition(Date()-[BillingPeriodStartDate],0,119,30)

I can figure out some of it but am wondering why there isn't a 60 or 90 in the formula for BillingPeriodStartDate that are 60 days out and 90 days out. Is this not needed?

Thanks,
Leesha

[HansV]

I used the little-known Partition function. The syntax of Partition is

Partition(value, start, stop, interval)

Here, value is the number of days from BillingPeriodStartDate to the current date.
start is the lowest value to group, here the obvious value 0.
stop is the highest value to group, here 119. Everything above that will be lumped into a single category, here 120: meaning 120 and above.
interval is the size of the groups, here 30. The first group will be 0 - 29, the second one 30 - 59 etc.

So Partition(Date()-[BillingPeriodStartDate],0,119,30) will divide the number of days into groups

0: 29
30: 59
60: 89
90:119
120:

[Leesha]

Thanks so much! The interval piece makes so much sense. I really appreciate the explanation.