Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing in a report (Access XP)

    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
    ****************************************

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Summing in a report (Access XP)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing in a report (Access XP)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summing in a report (Access XP)

    Try the following:
    1. <LI>Declare a variable in the module behind the report:

      Private mcurSum As Currency

      <LI>Create a function to retrieve the value of this variable:

      Private Function GetSum()
      GetSum = mcurSum
      End Function

      <LI>Create a text box in the Report Footer and set its Control Source to

      =GetSum()

      <LI>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.

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing in a report (Access XP)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summing in a report (Access XP)

    Colin,
    <hr>What really bugs me about this is that it is all so perfectly logical when it is explained to me!<hr>
    That happens to us all, I guess - I often get that feeling!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •