# Thread: Simple Sum Calculation

1. Registered User
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

2. Registered User
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.

3. Registered User
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. Registered User
Join Date
Oct 2003
Location
Roanoke, Va
Posts
445
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.

#### Posting Permissions

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