Results 1 to 11 of 11
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    128

    Sum an Aggregated Field

    I have a report that provides storage volume data for products in a warehouse. At the end of each day, the volume is recorded to a table. On my report, I have an average volume for each SKU for the report time period. I would like to have a sum of the averages at the bottom of the report. The problem is that I cannot seem to create an aggregate field of other aggregate fields.

    I tried various formula with no luck. Basically what I am trying to do is something like:

    Code:
    =Sum(Avg([InvVolume]))
    When I use this, it says "Cannot have aggregate function in expression Sum(Avg([InvVolume])).

    Can anyone provide some insight as to how I can sum the averages for each SKU?

    Note:I also tried adding a Total field from the Grouping & Totals, but it only allows me to add Number of Records.

    Thanks in advance for any assistance.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,607
    Have you tried to reference the control name instead of the formula for the total textbox? Lets say in the report detail you have a textbox named txtInvVolume with the control source = Avg([InvVolume]). In the report's footer add a new text box txtTotalInvVolume with the control source =Sum([txtInvVolume]).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    128
    Yes, I tried that, but it asked me to Enter Parameter Value for tbxInvVolume.

  4. #4
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    128
    Do I maybe need to reference the report name in the formula? Is so, could you tell me the syntax for that?

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,607
    Can you check your spelling, it should prompt you?
    Yes, I tried that, but it asked me to Enter Parameter Value for tbxInvVolume.
    Have you tried to reference the control name instead of the formula for the total textbox? Lets say in the report detail you have a textbox named txtInvVolume with the control source = Avg([InvVolume]). In the report's footer add a new text box txtTotalInvVolume with the control source =Sum([txtInvVolume]).
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    128
    My textboxes use the prefix tbx, not txt

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,607
    Sure thing, just a suggestion.
    Click image for larger version. 

Name:	Capture.PNG 
Views:	14 
Size:	54.6 KB 
ID:	44258
    I've just tried it and I get no prompt.
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    128
    The tbxInvVolume has a control source of =Avg([InvVolume]) and normally appears in the SKU Footer, not in the Details. Here is the whole report.

    Click image for larger version. 

Name:	screenshot db.PNG 
Views:	14 
Size:	15.7 KB 
ID:	44259

    And here is a shot with the fields expanded.

    Click image for larger version. 

Name:	screenshot db 2.PNG 
Views:	14 
Size:	10.1 KB 
ID:	44260

    I still get the prompt for Parameter Value for tbxInvVolume.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,607
    Please have a look at the attached file for two alternative ways of doing that. In one I created a totals query to do the averages by SKU then used dSum in the report footer's total control, in the other I added a new hidden control in the SKU footer and set the running sum to Over All then used that as the control source for the report footer box.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    128
    Thank you very much Gicu!! I went with the hidden field option, and it worked perfectly.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,607
    Sounds great, good luck with your project!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 3
    Last Post: 11-04-2020, 09:58 AM
  2. Calculation over an aggregated field
    By geotrouvetout67 in forum Access
    Replies: 3
    Last Post: 05-23-2019, 12:56 PM
  3. Multiple combo boxes for aggregated filtering
    By Mohibullah in forum Access
    Replies: 23
    Last Post: 02-22-2017, 08:26 AM
  4. Aggregated Sum
    By Shap in forum Queries
    Replies: 6
    Last Post: 06-16-2015, 02:36 PM
  5. Replies: 0
    Last Post: 04-14-2012, 07:36 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