Re: Calculated Mean

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

Date: Wed, 13 Oct 2004 17:04:29 -0400

Charles --

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. I cannot see what you're
actually putting into the SQL window, and thus can't see if there is
something going wrong with the copying process.

After you get the error, copy what is in the SQL window and paste it intoa
reply to this message....please. 

-- 
        Ken Snell
<MS ACCESS MVP>
"Charles" <Charles@discussions.microsoft.com> wrote in message
news:6A37D01C-73CF-46EA-84F0-A412629B9C19@microsoft.com...
> Hi Ken:
>
> I tried to create a new query.  On the Query screen, I clicked New, then
> Design View, then SQL. Then I pasted the statement to that screen. But an
> another error prompted:
>
> The SELECT statement includes a reserved word or an argument name that is
> misspelled or missing, or the punctuation is incorrect.
>
> I am terribly sorry for having bothered you so many times.  Is there any
way
> you could test on your Access program to see if there is anything I did
not
> follow correctly?  Thank you so much.
>
> Charles
>
>
>
>
> "Ken Snell [MVP]" wrote:
>
> > "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.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >.
> > > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Relevant Pages

  • Re: Calculated Mean
    ... you have been posting my SQL ... Let's start with a brand new query. ... window. ... >>> did the exactly as I did yesterday and I got the exact 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: 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)
  • Re: Change app.title at runtime?
    ... Ask yourself, "What Would SQL Do?" ... Note that our title *is* the Window Caption of window's owner! ... Private Declare Function GetWindowLong Lib "user32" Alias ... Private Function EnumThreadWndProc(ByVal hWnd As Long, ...
    (comp.lang.basic.visual.misc)