Results 1 to 3 of 3

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

  1. #1
    SusanCoder is offline Novice
    Windows 8 Access 2016
    Join Date
    Mar 2016
    Posts
    16

    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,638
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    SusanCoder is offline Novice
    Windows 8 Access 2016
    Join Date
    Mar 2016
    Posts
    16
    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, 06:00 AM
  2. Replies: 2
    Last Post: 10-07-2016, 03:50 PM
  3. Force Report Footer to before Page Footer
    By Charles CL in forum Reports
    Replies: 15
    Last Post: 01-21-2016, 11:06 AM
  4. Replies: 2
    Last Post: 12-21-2013, 01:09 PM
  5. Replies: 3
    Last Post: 02-17-2011, 09: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
  •  
Tech Forums: Microsoft Office Forums