Results 1 to 5 of 5
  1. #1
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85

    Unhappy Subtotal, Grand Total Sections in a report using Header/Footers to separate data

    I have a report that has 2 sections (Header/Footer). In the first section I have several plantings that each display the individual acreage for the entries listed. For the sake of simplicity say, 5 acres, 4 acres, 4 acres, and 5 acres. The cell information I used is [SumOfAcresPlanted] for this step

    In the first section, the footer is set up to subtotal the acreage for that plant variety, in this example 18 acres. This works just fine. There are several of these subsection that print in the report. In the footer for the first section, I entered =Sum[SumOfAcresPlanted) and it works perfectly

    However, when I get to the 2nd section, nothing I've tried will let me total the subtotals from each of the different varieties into one Grand total. Obviously =Sum(Sum[SumOfAcresPlanted]) is no good. All I've learned is that you can't nest a Sum Function within a Sum Function in Access 2003.

    I've also tried using the Text Box name from the cell in the first section in a Sum function with no success and an SQL where clause isn't working either.

    Can anyone give me some guidance on this?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You have GROUP section with Sum in GROUP footer? Did you try Sum in REPORT footer?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    Unfortunately, I don't think that a Sum in REPORT footer will work, because the Grand Total is not the end of the report. It's only the end of that Group. There are actually 4 group headers/footers in the report as follows:

    Vegetable Type - Most Detail (this is the level that sums correctly)
    Vegetable Group - Totals (this is the level I'm having a problem totaling)
    Location
    Season - Least Detail

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    A Sum at each group level should aggregate the data to that level.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    Believe it or not, I finally figured it out myself. It involve doing a separate query and attaching it to my main query, but it worked! Everything adds up.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Use a subtotal and %total
    By finlain in forum Programming
    Replies: 1
    Last Post: 03-28-2013, 09:36 AM
  2. How to Calculate this Grand Total
    By TWD in forum Reports
    Replies: 2
    Last Post: 01-13-2012, 11:36 AM
  3. Grand Total Field ???
    By damo1995 in forum Forms
    Replies: 6
    Last Post: 02-10-2011, 02:43 PM
  4. Grand total of field on a subreport
    By degras in forum Reports
    Replies: 9
    Last Post: 02-03-2011, 12:09 PM
  5. :?: newbie...grand total?
    By PnerraD in forum Access
    Replies: 1
    Last Post: 08-24-2006, 06:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums