Simple Sum Calculation but I can't work it out......

  • Thread starter Thread starter Kevin
  • Start date Start date
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.


Can anyone help!?

Many thanks

Chris
 
Maybe this is a stupid question(!) but how do I do that?

-----Original Message-----
No expert... but, I think you need to add [Revenue] to the group by
clause...

Cheers


Chris said:
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.


Can anyone help!?

Many thanks

Chris
Click to expand...


.
Click to expand...
 
I do it using straight SQL... so go to design view of your query... then
change to SQL view...

If you query is

select field1, Calculated Value from tblSomeTable

you can then add the group by clause to group related records together like
this:

SELECT Field1, CalculatedValue from tblSomeTable GROUP BY field1,
CalculatedValue

If you look at the SQL for your query and you have a group by clause, you'll
need to add the exact text you have doing the calculation. According to your
first post that'd be:

[Revenue]/sum([revenue])

I think you'll find all calculated values need to be in the group by clause
but data values don't... could be wrong...

By the way, you can do the grouping bit in straight design view but can't
remember how...

Post the SQL for your query if you get stuck...

Cheers


[email protected] said:
Maybe this is a stupid question(!) but how do I do that?

-----Original Message-----
No expert... but, I think you need to add [Revenue] to the group by
clause...

Cheers


Chris said:
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.


Can anyone help!?

Many thanks

Chris
Click to expand...


.
Click to expand...
Click to expand...
 
Yeah, you need to add the other fields in the select part of the query to
the group by clause... something like:

SELECT Test.[Customer Accounts], Sum(Test.Revenue) AS SumOfRevenue,
[revenue]/Sum([revenue]) AS [Percent]
FROM Test
GROUP BY Test.[Customer Accounts], SumOfRevenue, [Percent]

I think that should work now... try it... if not copy the entire select
section to the group by like so:

SELECT Test.[Customer Accounts], Sum(Test.Revenue) AS SumOfRevenue,
[revenue]/Sum([revenue]) AS [Percent]
FROM Test
GROUP BY Test.[Customer Accounts], Sum(Test.Revenue) AS SumOfRevenue,
[revenue]/Sum([revenue]) AS [Percent]

I think the first is correct and the second will probably give an error...
try it...

Cheers


Chris said:
Thanks Kevin

This is the SQL query I have:

SELECT Test.[Customer Accounts], Sum(Test.Revenue) AS
SumOfRevenue, [revenue]/Sum([revenue]) AS [Percent]
FROM Test
GROUP BY Test.[Customer Accounts];

It doesn't seem to want to work.
-----Original Message-----
I do it using straight SQL... so go to design view of your query... then
change to SQL view...

If you query is

select field1, Calculated Value from tblSomeTable

you can then add the group by clause to group related records together like
this:

SELECT Field1, CalculatedValue from tblSomeTable GROUP BY field1,
CalculatedValue

If you look at the SQL for your query and you have a group by clause, you'll
need to add the exact text you have doing the calculation. According to your
first post that'd be:

[Revenue]/sum([revenue])

I think you'll find all calculated values need to be in the group by clause
but data values don't... could be wrong...

By the way, you can do the grouping bit in straight design view but can't
remember how...

Post the SQL for your query if you get stuck...

Cheers


Maybe this is a stupid question(!) but how do I do that?


-----Original Message-----
No expert... but, I think you need to add [Revenue] to
the group by
clause...

Cheers


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.


Can anyone help!?

Many thanks

Chris


.
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks Kevin

This is the SQL query I have:

SELECT Test.[Customer Accounts], Sum(Test.Revenue) AS
SumOfRevenue, [revenue]/Sum([revenue]) AS [Percent]
FROM Test
GROUP BY Test.[Customer Accounts];

It doesn't seem to want to work.
-----Original Message-----
I do it using straight SQL... so go to design view of your query... then
change to SQL view...

If you query is

select field1, Calculated Value from tblSomeTable

you can then add the group by clause to group related records together like
this:

SELECT Field1, CalculatedValue from tblSomeTable GROUP BY field1,
CalculatedValue

If you look at the SQL for your query and you have a group by clause, you'll
need to add the exact text you have doing the calculation. According to your
first post that'd be:

[Revenue]/sum([revenue])

I think you'll find all calculated values need to be in the group by clause
but data values don't... could be wrong...

By the way, you can do the grouping bit in straight design view but can't
remember how...

Post the SQL for your query if you get stuck...

Cheers


Maybe this is a stupid question(!) but how do I do that?

-----Original Message-----
No expert... but, I think you need to add [Revenue] to the group by
clause...

Cheers


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.


Can anyone help!?

Many thanks

Chris


.
Click to expand...
Click to expand...


.
Click to expand...
 
Thanks for your help with this. Unfortunately neither
seemed to work!


-----Original Message-----
Yeah, you need to add the other fields in the select part of the query to
the group by clause... something like:

SELECT Test.[Customer Accounts], Sum(Test.Revenue) AS SumOfRevenue,
[revenue]/Sum([revenue]) AS [Percent]
FROM Test
GROUP BY Test.[Customer Accounts], SumOfRevenue, [Percent]

I think that should work now... try it... if not copy the entire select
section to the group by like so:

SELECT Test.[Customer Accounts], Sum(Test.Revenue) AS SumOfRevenue,
[revenue]/Sum([revenue]) AS [Percent]
FROM Test
GROUP BY Test.[Customer Accounts], Sum(Test.Revenue) AS SumOfRevenue,
[revenue]/Sum([revenue]) AS [Percent]

I think the first is correct and the second will probably give an error...
try it...

Cheers


Thanks Kevin

This is the SQL query I have:

SELECT Test.[Customer Accounts], Sum(Test.Revenue) AS
SumOfRevenue, [revenue]/Sum([revenue]) AS [Percent]
FROM Test
GROUP BY Test.[Customer Accounts];

It doesn't seem to want to work.
-----Original Message-----
I do it using straight SQL... so go to design view of your query... then
change to SQL view...

If you query is

select field1, Calculated Value from tblSomeTable

you can then add the group by clause to group related records together like
this:

SELECT Field1, CalculatedValue from tblSomeTable GROUP
Click to expand...
BY
field1,
CalculatedValue

If you look at the SQL for your query and you have a group by clause, you'll
need to add the exact text you have doing the calculation. According to your
first post that'd be:

[Revenue]/sum([revenue])

I think you'll find all calculated values need to be in the group by clause
but data values don't... could be wrong...

By the way, you can do the grouping bit in straight design view but can't
remember how...

Post the SQL for your query if you get stuck...

Cheers


Maybe this is a stupid question(!) but how do I do that?


-----Original Message-----
No expert... but, I think you need to add [Revenue] to
the group by
clause...

Cheers


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.


Can anyone help!?

Many thanks

Chris


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
CLick on the totals button (looks like a fancy E its next
to the Show table button on the tool bar) then
select "Expression" instead of "Group by" in the Totals
row under your expression.

Other than that I cant help! Hope that works for ya.

Marcelle.
 
Chris,

One strategy I frequently find effective, not being an SQL
expert, is Divide and Conquer, which may sacrifice
elegance and speed, but neither of which I've ever found
significant for small to midsize single user or small
network applications.

Create two Totals queries (View, Totals from Query Design
View):

Query1 (Total Revenue per Customer)

Field Value In Total Row
CustomerNumber Group By
Revenue Sum

Query2 (Total Revenue)
Field Value In Total Row
Revenue Sum

Now create a select query with the following fields:

Query1.CustomerNumber
Query1.SumofRevenue
[Percentage]: (Query1.SumofRevenue/Query2.SumofRevenue)*100

The SQL looks like:

Query1
======
SELECT Revenue.CustomerNumber, Sum(Revenue.Revenue) AS
SumOfRevenue
FROM Revenue
GROUP BY Revenue.CustomerNumber;

Query2
======
SELECT Sum(Revenue.Revenue) AS SumOfRevenue
FROM Revenue;

Query3
======
SELECT qryRevenue1.CustomerNumber,
qryRevenue1.SumOfRevenue,
(qryRevenue1.SumofRevenue/qryRevenue2.SumofRevenue)*100 AS
[Percent]
FROM qryRevenue1, qryRevenue2;
 
When you try to use an expression such as this one you
need to remember that you are dealing with objects "the
table names" so it should be rewritten as follow

Percent: [table name]![Revenue]/sum([table name]![revenue]

even if you are using one table in your query, you should
follow the above format. Hope it works
 
Back
Top