I've got the RUNNING SUM Blues!!!

[rnickels]

Running Sum Blues!!!

Hello,

I have a report which is similar to a bank statement. I have a transaction date and then either a credit or debit depending on whether the customer made a payment or if I am invoicing them.


I have created an additional field in the report which calculates AmountDue - TotalPaymentAmount and called this field Balance.
I then attributed a running sum based on this Balance field.
This works great.................BUT!!!!!
If a customer has 100 transactions the report will be well over 10 pages long.
Is there a way I can limit the report to just perhaps the most recent 20 transactions or 20 days?

I tried and failed in the report's query retrieve the payments which occur >datenow(-20) (basically in the last 20 days). This worked ok in the sense that only the last 20 days worth of transactions appeared in the report......BUT!......the first running sum of the report does not carry over from the previous transaction..it starts from 0!!!!

Please help me get over the RUNNING SUM blues!
Rob

 

[neileg]

You could use a totals query to calculate the balance at the start of the period and then union that with the query you use to extract your transactions. There may be a more 'elegant' way but that should work.

 

[Dennisk]

use the TOP keyword

eg strGetSQL = "SELECT TOP 10 Products.[ProductName] " _
& "AS TenMostExpensiveProducts, Products.UnitPrice FROM Products " _
& "ORDER BY Products.[UnitPrice] DESC;"
this will return the first 10 rows

 

[stallyon]

you could always use a DSum Function to get the sum of all transactions before the first date on the report

 

[rpadams]

Use the On Format of the report

Assuming that all of the transactions are being shown in the Detail section of your report, set an event in the Detail Format for the report. I select by a date that is no earlier than a date I select in the form that I use to call the report.

For example:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [CheckDate] >= Forms!frmMain!txtDateSelect
Me.Detail.Visible = True
Else
Me.Detail.Visible = False

End If
End Sub

I then use ALL of the transactions to maintain an accurate running balance but show only a limited amount of the most recent. With only a few hundred, or thousand, you won't even be aware of the overhead time it takes.