Summing in a report (Access XP)

Topic

New Reply

I have a report which scans all the data in my database and produces a summary of the monthly income for each month of the year.

The report is grouped by each month, and no “details are shown, giving me a simple month and a total income for the year.

In the report Footer however I do a number of aggregate calculations, and one of these indicates the predicted income for the full year, and had used the average “sum” of Monthly contributions as the predicted amount for the remaining months. However, as my monthly income has been increasing through the year, the current level of monthly contributions is significantly higher than the average. As we get near the year end, this makes quite a significant difference to the report.

I can’t think of a way to use the last value of the “sum”, ie that for the current month. This would give me the most accurate prediction for the year.

Does anyone have any suggestions.

Thanks

Colin
****************************************
Colin McDonald
colinmac@blueyonder.co.uk
****************************************

Reply | Quote

Replies

In the format event of the detail section just save the value of the “sum” in a variable defined just after the Option Explicit.
You can subsequently use this in your report footer calcs.
Pat

Reply | Quote

Sounds simple.

my Sum in the report is called Sum of Donation and it = Sum([PropAnn])

I tried creating a strSum in the detail event and tried it as Sum([PropAnn]) and alternatively as Sum of Donation, but neither works.

How should I allocate the variable, and how do I then call it on the report footer.

Colin

Reply | Quote

Try the following:

1. Declare a variable in the module behind the report:

   Private mcurSum As Currency

2. Create a function to retrieve the value of this variable:

   Private Function GetSum()
   GetSum = mcurSum
   End Function

3. Create a text box in the Report Footer and set its Control Source to

   =GetSum()

4. Set mcurSum in the OnFormat event of the group footer section:

   Private Sub GroupFooter_Format(Cancel As Integer, FormatCount As Integer)
   mcurSum = [Sum of Donation]
   End Sub

   (the actual name of the group footer on your report may be different)

This way, the text box in the report footer should display the sum for the last (most recent) month in the report.

Reply | Quote

Thanks very much. That has worked perfectly.

What really bugs me about this is that it is all so perfectly logical when it is explained to me!

I know I have learned a lot about access in the last 18 months, and I can create simple programs, but I guess that 99.9% of access is still beyone me.

Ah well I’ll keep learning.

Thansk to you and Pat for the comments. I hope that I learn something from this one.

Colin

Reply | Quote

Colin,[indent]

---

What really bugs me about this is that it is all so perfectly logical when it is explained to me!

---

[/indent]That happens to us all, I guess – I often get that feeling!

Reply | Quote