Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: Simple Sum Calculation

    I am new to Access and am trying to insert a simple
    calculated field in a query but I am having difficulty.

    I have one field of all customers and another of all
    revenue associated to that customer. I want to insert a
    calculated field that will give me a % of total revenue
    for each customer.

    Here is what I am trying:

    Percent: [Revenue]/sum([revenue])

    This keeps giving me the following error message:

    You tried to execute a query that does not include the
    specified expression 'Customer Account' as part of an
    aggregate function.

    I've attached a picture of the screenshot problem.


    Can anyone help!?

    Many thanks

    Chris
    Attached Thumbnails Attached Thumbnails sum problem.gif  

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: Simple Sum Calculation

    Originally posted by cpevans
    I am new to Access and am trying to insert a simple
    calculated field in a query but I am having difficulty.

    I have one field of all customers and another of all
    revenue associated to that customer. I want to insert a
    calculated field that will give me a % of total revenue
    for each customer.

    Here is what I am trying:

    Percent: [Revenue]/sum([revenue])

    This keeps giving me the following error message:

    You tried to execute a query that does not include the
    specified expression 'Customer Account' as part of an
    aggregate function.

    I've attached a picture of the screenshot problem.


    Can anyone help!?

    Many thanks

    Chris
    Select Totals from the View menu to start. When you aggregate data you need to group by anything that is not a part of the aggregate function. When you select totals, it will change your Percent fields also to Group By. I think that you need to change that to Expression. This should be the simple fix.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  3. #3
    Join Date
    Mar 2004
    Posts
    2
    Thanks,

    The query will at least run now, but it gives all of the results as 100%!

    It doesn't calculate the sum of revenue correctly. For example,

    If total customer revenue = 100

    and Customer A revenue = 10

    The formula is calculating customer A revenue as a proportion of total customer A revenue and thus calculating 10/10 so every result is 100%

    Any ideas how to work around this?
    Last edited by cpevans; 03-05-04 at 08:21.

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Cool

    Originally posted by cpevans
    Thanks,

    The query will at least run now, but it gives all of the results as 100%!

    It doesn't calculate the sum of revenue correctly. For example,

    If total customer revenue = 100

    and Customer A revenue = 10

    The formula is calculating customer A revenue as a proportion of total customer A revenue and thus calculating 10/10 so every result is 100%

    Any ideas how to work around this?
    Try this:

    Set up a separate query using the table that has the revenue totals that you want and just include one field. Get the total for all of the customers. Sum(CustomerRevenue). There should only be one field and one value.

    Now, set up another query and get just the Total for each customer by summing their revenue and grouping by the Customer. You should have two fields in this query or more only if you want to include customer information.

    Last, create your query that will calculate the amounts that you need by putting the first two queries in the design grid with no join line between them. You are basically creating a cartesian product but it's OK in this case because there is only one value in the first query. Go ahead and pull down the customer information from the second query and do your division by using the values from both queries. You don't even need to group.

    It may not be pretty, but it works.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •