Results 1 to 10 of 10

Grand total of field on a subreport

  1. #1
    degras is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    72

    Grand total of field on a subreport

    How is it possible to calculate and show a grand total of a totals field from a subreport?

    e.g. looking something like the following

    Record 1
    Subreport Record 1 ... Total: 7
    Subreport Record 2 ... Total: 10
    Subreport total: 17

    Record 2
    Subreport Record 3 ... Total: 12
    Subreport Record 4 ... Total: 8
    Subreport total: 20

    Report Grand Total: 37

    I don't know how to reference the totals field (on the subreport) from the main report.
    I tried Sum(Me!ItemsSubreport.Report!Totals) but the editor put brackets around each word and then I was asked for the value in a dialogue when I ran the report.
    Last edited by degras; 02-01-2011 at 08:07 AM.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,335
    The syntax is the same as forms:

    Forms Refer to Form and Subform properties and controls

    substituting Reports/Report for Forms/Form. Me is only valid in VBA, so would not work in a control source. You can also right click in the textbox and choose Build. Navigating to the subreport will build the correct syntax.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    degras is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    72
    Quote Originally Posted by pbaldy View Post
    The syntax is the same as forms:

    Forms Refer to Form and Subform properties and controls

    substituting Reports/Report for Forms/Form. Me is only valid in VBA, so would not work in a control source. You can also right click in the textbox and choose Build. Navigating to the subreport will build the correct syntax.
    I don't see how that will work. Nothing seems to work as a control source with Sum(x), Access asumes that x is a field or a parameter. Anything you put in place of x and Access usually puts square brackets around it, turning it into a report parameter.

    I could not find Sum in the expression builder.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,335
    You would just refer to the subreport textbox that contains the value you want.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  5. #5
    degras is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    72
    Quote Originally Posted by pbaldy View Post
    You would just refer to the subreport textbox that contains the value you want.
    I don't know what you mean, because the sum (grand total) would need to be calculated over all subreports, not just one subreport.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,335
    Well, you said "a subreport", but it really doesn't matter. The Sum() needs to happen in the subreport. In the main report instead of =Sum(...) you just refer to the textbox with the grand total in the subreport.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  7. #7
    degras is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    72
    You are getting the grand total and the total confused. Please look again at my example. The grand total is not in the subreport, it needs to be on the main report as it is the sum of the totals over all subreports.

    I am having problems referring to controls from within the Sum function, Access interprets the control as a parameter.

    If I add a group footer with a total then Access only allows sums of a field in the table in the main report not the subreport. I could use this method if I could store the total from the subreport as a field on the main table. The problem would be: how would I get this field updated?

    I don't think I can use DSum either, as the company name (that is the grouping for the grand total) is not stored on the subreports table. In any case the filter for DSum would have to come from the a reported field and I am not sure this is possible.

    Or is there some other way of doing this grand total?
    ...

    I solved this, in the end I used a totals field on the main table and updated it with the value of a Sum control on the subreport. I had to use Me.Recalc to make sure the Sum was recalculated.
    Last edited by degras; 02-03-2011 at 10:07 AM.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,335
    As you've found, you can't refer to a control in a Sum() formula. I expected you would have =Sum(...) in the subreport's report footer section in addition to the group footer. It's that textbox in the report footer you would refer to from the main report.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  9. #9
    degras is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    72
    Thanks for your help.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,335
    No problem; sorry for the confusion.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

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

Similar Threads

  1. Adding a total to a subreport?
    By degras in forum Reports
    Replies: 3
    Last Post: 01-25-2011, 08:30 AM
  2. Replies: 3
    Last Post: 06-23-2010, 01:02 PM
  3. Total field with access
    By rev_ollie in forum Queries
    Replies: 0
    Last Post: 04-27-2010, 07:54 AM
  4. Subreport Calculation field
    By Cheshire101 in forum Reports
    Replies: 2
    Last Post: 01-14-2010, 03:50 PM
  5. :?: newbie...grand total?
    By PnerraD in forum Access
    Replies: 1
    Last Post: 08-24-2006, 05: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
  •  
Tech Forums: Microsoft Office Forums