Re: Calculated Mean

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 10/13/04

Next message: Joy Rose: "Re: Return repeats info in "8s""
Previous message: Joy Rose: "Re: Return repeats info in "8s""
In reply to: Charles: "Re: Calculated Mean"
Next in thread: Charles: "Re: Calculated Mean"
Reply: Charles: "Re: Calculated Mean"
Messages sorted by: [ date ] [ thread ]

Date: Wed, 13 Oct 2004 11:57:38 -0400

"When I copied the statement to the Field ..."

I just realized what you're saying ... you have been posting my SQL
statement in the wrong place.

Let's start with a brand new query. Create a new query, don't select any
tables, close the table window, click on view icon at top left of toolbar
and select SQL, and paste the SQL statement that I posted into the displayed
window (replace any text already there). Then try the query.

-- 
        Ken Snell
<MS ACCESS MVP>
"Charles" <Charles@discussions.microsoft.com> wrote in message
news:05909F13-EA3A-4D1E-95FA-F97EA65AB667@microsoft.com...
> Hi Ken:
>
> I am terribly sorry for this chronicle hassile.  I tried it again this
> morning and got the following error messages:
>
> When I copied the statement to the Field (either put M1: or not prior to
the
> statement), the query window prompted an error message:
>
> The syntax of the subquery in this expression is incorrect.
> Check the subquery's syntax and enclose the subquery in parentheses.
>
> When I put a pair of parentheses ( ), immediately before the first word
> SELECT and immediately after the last word Table1 (semi column ;
disappeared
> automatically), the query window prompted:
>
> The expression you entered contains invalid syntax.
> You may have entered an operand without an operator.
>
> I know there may be other problem than the statement but I checked the
> Table1, variable Dept is text and G1, G2, G3 are all number.  It seems
> everything is fine. But why the statement does not work. I sincerely
> appreciate your time in helping me with this. Thank you very much.
>
>
> Charles
>
>
>
> "Ken Snell [MVP]" wrote:
>
> > Either something is going wrong when you paste the SQL statement into
your
> > query's window, or something about your setup is different from what I'm
> > understanding.
> >
> > Post here the exact statement that your query says has an error in the
SQL
> > (don't post back what I posted, but rather what is in your query's
window
> > when the error occurs).
> > -- 
> >
> >         Ken Snell
> > <MS ACCESS MVP>
> >
> >
> >
> > "Charles" <Charles@discussions.microsoft.com> wrote in message
> > news:1D1EDD04-2D59-4A1E-B5C3-03E51086CC98@microsoft.com...
> > > Hi Ken:
> > >
> > > Thank you so much for your continuous help. But the problem still
exist.
> > I
> > > did the exactly as I did yesterday and I got the exact error messages
as I
> > > received yeasterday.  What is the reason for this lasting problem?
Could
> > you
> > > help me check again? I really appreciate your time.
> > >
> > > Charles
> > >
> > >
> > >
> > >
> > > "Ken Snell [MVP]" wrote:
> > >
> > > > Oh I think I see what I missed -- try this (sorry.... have been
overly
> > tired
> > > > today!):
> > > >
> > > > SELECT Table1.Dept, Table1.G1, Table1.G2, Table1.G3,
> > > > (SELECT Avg(T.G1) FROM Table1 AS T
> > > > WHERE T.Dept = Table1.Dept) AS M1,
> > > > (SELECT Avg(U.G2) FROM Table1 AS U
> > > > WHERE U.Dept = Table1.Dept) AS M2,
> > > > (SELECT Avg(V.G3) FROM Table1 AS V
> > > > WHERE V.Dept = Table1.Dept) AS M3,
> > > > FROM Table1
> > > > GROUP BY Table1.Dept, Table1.G1, Table1.G2,
> > > > Table1.G3
> > > > ORDER BY Table1.Dept;
> > > > -- 
> > > >
> > > >         Ken Snell
> > > > <MS ACCESS MVP>
> > > >
> > > >
> > > >
> > > >
> > > > "Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in
message
> > > > news:OW%23UcBAsEHA.3964@TK2MSFTNGP10.phx.gbl...
> > > > > No, don't copy to field row. Create a new query, don't select any
> > tables,
> > > > > close the table window, click on view icon at top left of toolbar
and
> > > > select
> > > > > SQL, and paste the SQL statement that I posted into the displayed
> > window
> > > > > (replace any text already there). Then try the query.
> > > > >
> > > > > -- 
> > > > >
> > > > >         Ken Snell
> > > > > <MS ACCESS MVP>
> > > > >
> > > > > "Charles Deng" <Charles Deng@discussions.microsoft.com> wrote in
> > message
> > > > > news:B63AA8D9-6A3F-456C-B34B-A29EC8A28680@microsoft.com...
> > > > > > Hi Ken:
> > > > > >
> > > > > > Thank you so much for this subquery.  But it still does not work
> > yet. I
> > > > > > copied this statement to the Field row, I got a error message:
> > > > > >
> > > > > > Check the subquery's syntax and enclose the subquery in
parentheses.
> > > > > >
> > > > > > When I put a pair of parentheses, the following error message
> > prompted
> > > > > again:
> > > > > >
> > > > > > You may have entered an operand without an operator.
> > > > > >
> > > > > > I checked this statement again and again but I am not able to
find
> > an
> > > > > error.
> > > > > >  But when I copied this statement to SQL View (I know I am
supposed
> > to
> > > > do
> > > > > > so), I got the following error message:
> > > > > >
> > > > > > The SELECT statement includes a reserved word or an argument
name
> > that
> > > > is
> > > > > > misspelled or missing, or the punctuation is incorrect.
> > > > > >
> > > > > > But I still have not any clue where the problem is.  Could you
check
> > the
> > > > > > statement again to see where the problem is? Thank you very
much.
> > > > > >
> > > > > > Charles
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Ken Snell [MVP]" wrote:
> > > > > >
> > > > > > > ahhhh....
> > > > > > >
> > > > > > > Try this (uses a subquery):
> > > > > > >
> > > > > > > SELECT Table1.Dept, Table1.G1, Table1.G2, Table1.G3,
> > > > > > > (SELECT Avg(T.G1) FROM Table1 AS T
> > > > > > > WHERE T.Dept = Table1.Dept) AS M1,
> > > > > > > (SELECT Avg(U.G2) FROM Table1 AS U
> > > > > > > WHERE U.Dept = Table1.Dept) AS M2,
> > > > > > > (SELECT Avg(V.G3) FROM Table1 AS V
> > > > > > > WHERE V.Dept = Table1.Dept) AS M3,
> > > > > > > FROM Table1
> > > > > > > GROUP BY Table1.Dept;
> > > > > > >
> > > > > > > -- 
> > > > > > >
> > > > > > >         Ken Snell
> > > > > > > <MS ACCESS MVP>
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Charles Deng" <anonymous@discussions.microsoft.com> wrote in
> > message
> > > > > > > news:339d01c4af1e$7f67b840$a401280a@phx.gbl...
> > > > > > > > Hi Ken:
> > > > > > > >
> > > > > > > > Thanks for your interest in this:
> > > > > > > >
> > > > > > > > SELECT Table1.Dept, Table1.G1, Table1.G2, Table1.G3, Avg
> > > > > > > > (Table1.G1) AS Expr1
> > > > > > > > FROM Table1
> > > > > > > > GROUP BY Table1.Dept, Table1.G1, Table1.G2, Table1.G3
> > > > > > > > ORDER BY Table1.Dept;
> > > > > > > >
> > > > > > > >
> > > > > > > > Charles
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > >-----Original Message-----
> > > > > > > > >Post the SQL of the query that you tried to use.
> > > > > > > > >
> > > > > > > > >-- 
> > > > > > > > >
> > > > > > > > >        Ken Snell
> > > > > > > > ><MS ACCESS MVP>
> > > > > > > > >
> > > > > > > > >"Charles Deng" <anonymous@discussions.microsoft.com>
> > > > > > > > wrote in message
> > > > > > > > >news:329e01c4aefd$bcb1bbe0$a401280a@phx.gbl...
> > > > > > > > >> Hi All:
> > > > > > > > >>
> > > > > > > > >> I have a query like:
> > > > > > > > >>
> > > > > > > > >> Dept Sec   G1   G2   G3   M1   M2   M3
> > > > > > > > >> ACC  100  3.5  2.8  3.2
> > > > > > > > >> ACC  100  2.6  3.1  3.8
> > > > > > > > >> ACC  200  3.5  2.8  3.2
> > > > > > > > >> ACC  300  2.6  3.1  3.8
> > > > > > > > >> BUS  100  2.6  3.1  3.8
> > > > > > > > >> BUS  100  3.5  2.8  3.2
> > > > > > > > >> BUS  200  2.6  3.1  3.8
> > > > > > > > >> BUS  200  3.5  2.8  3.2
> > > > > > > > >>
> > > > > > > > >> I need to add three calculated fields to this query M1,
> > > > > > > > >> M2, and M3.  M1 is mean of G1, M2 is mean of G2, and M3
> > > > > > > > is
> > > > > > > > >> mean of G3.  All these means are grouped by Dept.  What
> > > > > > > > I
> > > > > > > > >> did is:
> > > > > > > > >>
> > > > > > > > >> put M1:=avg([G1]) in Field and Group by: [Dept] in
> > > > > > > > Total.
> > > > > > > > >> But the access does not accept.  What is wrong with
> > > > > > > > what I
> > > > > > > > >> did? or what are correct expression I need to type in?
> > > > > > > > >> Thanks a lot.
> > > > > > > > >>
> > > > > > > > >>
> > > > > > > > >> Charles
> > > > > > > > >>
> > > > > > > > >>
> > > > > > > > >> I need to create a new field in the query.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >.
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Next message: Joy Rose: "Re: Return repeats info in "8s""
Previous message: Joy Rose: "Re: Return repeats info in "8s""
In reply to: Charles: "Re: Calculated Mean"
Next in thread: Charles: "Re: Calculated Mean"
Reply: Charles: "Re: Calculated Mean"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Re: Calculated Mean
... As I have asked a number of times, you need to copy what you have in the SQL ... window and paste it back here in a message. ... On the Query screen, I clicked New, then> Design View, then SQL. ... I tried it again this>>> morning and got the following error messages: ...
(microsoft.public.access.queries)
Re: One or Multiple Criteria Search Form
... The query window is much better for debugging SQL statements because you get ... Then, when the code stops at the breakpoint, examine strWhere in the ...
(microsoft.public.access.formscoding)
Re: add hint in access query
... With the query design grid that opens, a pop up window entitled "Show Table" appears. ... Close the pop up without choosing anything and on the Query menu select SQL SPecific -> Pass-Through. ... Wei, not sure how clear I was on this, but the SQL written in any pass-through query would be the Oracle dialect you are used to working with. ...
(comp.databases.ms-access)
Re: Select only negative numbered query results
... Here's the SQL again with a Where clause for the criteria. ... all the ones I tried because I kept getting error messages or pop up windows ... If you are working in query design ...
(microsoft.public.access.gettingstarted)
Re: Calculated Mean
... I tried to create a new query. ... Design View, then SQL. ... > window. ... >> Check the subquery's syntax and enclose the subquery in parentheses. ...
(microsoft.public.access.queries)