Results 1 to 3 of 3
  1. #1
    SusanCoder is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Mar 2016
    Posts
    30

    Compute sum in main report Group footer on value from sub report

    Thanks for your help. Trying to sum participant #s in main report. Participant #s are computed in the subreport.



    The main report named zrptAllTrTypesTrainingsByDateDirectPart is grouped by Training Type, and the detail section looks like this and will have several trainings under each training type:

    TrainingType
    (grouped by, header)

    TrainingBeginDate CityState
    txtDirectPartSum [zsubrptAllTrTypesByDateDirectPart].[txtDirectParticipantNum] Instructor

    Within the subreport zsubrptAllTrTypesByDateDirectPart,the number of participants is stored in a field named txtDirectParticipantNum. The control source is a query that sums the participants for each training from a table. The participant numbers are entered by groups that attend a specific training, like Teachers (5) + Principals (10) = (15) participants for training X. For the specific training in this example, txtDirectParticipantNum's value would be 15.

    I'm trying to sum the number of participants in the main report, in the TrainingType footer.

    This is what's working so far...

    I'm storing the # of participants on the main report in each detail line in a calculated text box called txtDirectPartSum (which will not be visible).I get the value for this unbound field from the subreport using this function: =IIf([zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[HasData],Nz([zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[txtDirectParticipantNum],0),0). This works great. I thought storing the value on the main form would make it easier to sum in the TrainingType footer.

    What's not working...summing txtDirectPartSum in the TrainingType footer.

    I have an unbound calculated text box named txtSummedDirectPartSum in the TrainingType footer to sum all of the participant numbers in the training type section (all the txtDirectPartSum values). I've tried the following functions inside txtSummedDirectPartSum and can't get any to work.

    Code:
     =Sum([txtDirectPartSum])
    Code:
    =Sum([zrptAllTrTypesTrainingsByDateDirectPart].[txtDirectPartSum])
    Code:
    =Sum(IIf([zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[HasData],Nz([zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[txtDirectParticipantNum],0),0))
    Code:
    =Sum(IIf([zrptAllTrTypesTrainingsByDateDirectPart].[Report].[zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[HasData],Nz([zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[txtDirectParticipantNum],0),0))
    Code:
    =Sum(IIf([zrptAllTrTypesTrainingsByDateDirectPart].[Report].[zsubrptAllTrTypesTrainingsByDateDirectPart].[HasData],Nz([zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[txtDirectParticipantNum],0),0))
    In each case, Access is not recognizing the syntax after =(Sum(, or between =Sum(IIf([ and ,Nz.


    fyi: txtDirectPartSum and txtSummedDirectPartSum and txtDirectParticipantNum are formatted as Standard, no decimals.

    Thanks again for your help!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Aggregate calcs must reference fields, not controls. Are expressions referencing actual field names or are those names of textboxes?

    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.

  3. #3
    SusanCoder is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Mar 2016
    Posts
    30
    Thank you, June7. Your statement "aggregate calcs must reference fields, not controls" helped me find the answer. I was trying to reference controls on the subreport to do calculations in the main report. Instead, I queried the tables for the sub and grand total data required, created subreports with the sub and grand total fields from the queries, and added the subreports to my main report. It worked! Thank you, again.

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

Similar Threads

  1. Replies: 1
    Last Post: 10-14-2016, 07:00 AM
  2. Replies: 2
    Last Post: 10-07-2016, 04:50 PM
  3. Force Report Footer to before Page Footer
    By Charles CL in forum Reports
    Replies: 15
    Last Post: 01-21-2016, 12:06 PM
  4. Replies: 2
    Last Post: 12-21-2013, 02:09 PM
  5. Replies: 3
    Last Post: 02-17-2011, 10:19 AM

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