Results 1 to 4 of 4
  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108

    Calculation over an aggregated field

    Hello,

    I have a report fetching from a query in which one of the fields is aggregated, SumofOrderQty

    In my report I wanted to perform a simple calculation on this field like =[SumofOrderQty]/1000 but it returns a type error so apparently Access can't perform a calculation on a calculation, what would be the work around?


    Thank you

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Just add the calculated field in your query, and Sum on that calculated field too, i.e.
    Code:
    Expr1: [OrderQty]/1000
    Then it will be available to pull into your report without having to do the calculation on the report.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Ah, bingo! It works that way, thank you.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome.

    I prefer to do most (if not all) the calcs at the query level instead of the Form/Report level (of course, if you are doing Group subtotals, then you would have to do those in the Group Footer).
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

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

Similar Threads

  1. Limiting date ranges & aggregated query
    By Forbes in forum Reports
    Replies: 25
    Last Post: 03-29-2017, 12:31 PM
  2. Multiple combo boxes for aggregated filtering
    By Mohibullah in forum Access
    Replies: 23
    Last Post: 02-22-2017, 08:26 AM
  3. Aggregated Sum
    By Shap in forum Queries
    Replies: 6
    Last Post: 06-16-2015, 02:36 PM
  4. Calculation Field in table vs Query calculation
    By Leonidsg in forum Database Design
    Replies: 18
    Last Post: 02-28-2013, 09:38 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