Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Aug 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum on report (Access 2002)

    Hello,

    I trying to assist someone in creating a financial report which is fairly simple but needs to sum up all of the dollars in the "revenue" fields that appear on the form. I tried using a text box with a control of "=SUM([revenue})" but that did not do the trick. What do I need to do to sum up all of the revenue fields on a report? BTW this report runs off of a simple query that pulls certain records from the main table if that matters? Any help appreciated! Thanks for taking the time to help a newbie-dork.

    K

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum on report (Access 2002)

    Hi

    Assuming the form is bound to your query this should work. This is how I do it:
    1) Create an unbound text box (toolbox wizard off) and place it on the form
    2) Go to the properties of the new text box and click on the Data tab
    3) Type in =sum([revenue]) in the Control Source

    If there is a problem with the sum it may be worth putting in the form footer or header first just to if it works.

    It may be worth thinking about creating the form as normal and then from the same query create the report and repeat the steps above again in the report footer area.
    Jerry

  3. #3
    Lounger
    Join Date
    Aug 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum on report (Access 2002)

    Thanks, I had the text box in the Page Footer as opposed to the form footer and I guess that made the difference. However, I am still having an issue you may be able to help with? How do I get a subtotal under various sub headings that totals only records with the sub heading?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum on report (Access 2002)

    I have been doing a project recently where I had to set up a postal log (yawn) for a small team in my company. They wanted the total number of pieces of post answered within a certain period. By far the easiest way I found doing the counts for different types of post ( internal, external, complaints etc) was to use the cross tab query wizard. once created I then created a report from it.

    Think about how you create your original table fields which the CTQ will reference to. I sometimes prefer to create a CTQ from a main query so that I can put parameters in such as start and finish date.

    I am looking for the simple solution, but I am sure there may be a few more in the lounge somewhere.
    Jerry

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum on report (Access 2002)

    Choose the Sorting and Grouping Icon on the toolbar in your report design view. Double-click Group Footer "NO", to turn it into a "Yes". (This is if you in fact while using the Report Wizard "did in fact" group by ...some... field. Then paste your text box into your newly created grouped footer, and type in the same formula that you've been given... = sum([Revenue])
    This will create a sum for each group. If you want it to be a running sum, then right-click on this same text box with the formula in it, and choose "properties", and then the ALL tab, and choose the Running Sum line and then "Over All" or "over Group" which ever seves you better from the list...close the properties sheet, look at your report in preview. Hope this helps
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum on report (Access 2002)

    I like yours better
    Jerry

  7. #7
    Lounger
    Join Date
    Aug 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum on report (Access 2002)

    That did the trick! Thank you very much!

    K

Posting Permissions

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