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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Chris (anonymous_at_discussions.microsoft.com)
Date: 03/05/04 

Date: Fri, 5 Mar 2004 03:05:36 -0800

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
>
>
>"cpevans@aol.com" <anonymous@discussions.microsoft.com>
wrote in message
>news:778601c4029e$fcac86f0$a001280a@phx.gbl...
>> 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" <cpevans@aol.com> wrote in message
>> >news:785501c4029c$f5550500$a401280a@phx.gbl...
>> >> 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
>> >
>> >
>> >.
>> >
>
>
>.
>

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)