Calculated Mean

Charles Deng · Oct 10, 2004

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.

Ken Snell [MVP] · Oct 10, 2004

Post the SQL of the query that you tried to use.

Charles Deng · Oct 11, 2004

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>

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.

Click to expand...

.

Ken Snell [MVP] · Oct 11, 2004

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

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.

Click to expand...

.

Click to expand...

Guest · Oct 12, 2004

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

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>

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.

.

Click to expand...

Click to expand...

Guest · Oct 12, 2004

Hi Ken:

Thank you so much for your help. But the statement does not work yet. I
copied this statement to the Field row, the following error message prompted:

Check the subquery's syntax and enclose the subquery in parentheses.

When I put a pair of parentheses to the statement, another error message
prompted:

You may have entered an operand without an operator.

There is no way I can fix this problem and I tried to see what would happen
if I copy this statement to SQL view (I know I am not 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.

I really could not find any clue about this problem. Could you take time to
check this statement again? Thank you very very much.

Charles

Ken Snell said:
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 said:

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>

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.

.

Click to expand...

Click to expand...

Ken Snell [MVP] · Oct 12, 2004

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

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

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" <[email protected]>
wrote in message
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.

.

Click to expand...

Click to expand...

Ken Snell [MVP] · Oct 12, 2004

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

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

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>

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" <[email protected]>
wrote in message
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.

.

Click to expand...

Click to expand...

Guest · Oct 12, 2004

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

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

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

:

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>

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" <[email protected]>
wrote in message
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.

.

Click to expand...

Click to expand...

Ken Snell [MVP] · Oct 13, 2004

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

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

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>

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

Click to expand...

to
do

so), I got the following error message:

The SELECT statement includes a reserved word or an argument name

Click to expand...

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

:

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>

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" <[email protected]>
wrote in message
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.

.

Click to expand...

Click to expand...

Guest · Oct 13, 2004

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

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

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>

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>

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

:

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>

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" <[email protected]>
wrote in message
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.

.

Click to expand...

Click to expand...

Ken Snell [MVP] · Oct 13, 2004

Please...please post the actual SQL that you get the error message from....
I cannot see your database through my PC monitor....

--

Ken Snell
<MS ACCESS MVP>

Charles said:
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 said:

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

Hi Ken:

Thank you so much for your continuous help. But the problem still

Click to expand...

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?

Click to expand...

Could
you

help me check again? I really appreciate your time.

Charles

:

Oh I think I see what I missed -- try this (sorry.... have been

Click to expand...

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>

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>

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

Click to expand...

find
an

error.
But when I copied this statement to SQL View (I know I am

Click to expand...

supposed
to

do
so), I got the following error message:

The SELECT statement includes a reserved word or an argument

Click to expand...

name
that

is
misspelled or missing, or the punctuation is incorrect.

But I still have not any clue where the problem is. Could you

Click to expand...

check
the

statement again to see where the problem is? Thank you very much.

Charles

:

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>

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" <[email protected]>
wrote in message
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.

.

Click to expand...

Click to expand...

Ken Snell [MVP] · Oct 13, 2004

"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 said:
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 said:

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

Hi Ken:

Thank you so much for your continuous help. But the problem still

Click to expand...

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?

Click to expand...

Could
you

help me check again? I really appreciate your time.

Charles

:

Oh I think I see what I missed -- try this (sorry.... have been

Click to expand...

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>

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>

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

Click to expand...

find
an

error.
But when I copied this statement to SQL View (I know I am

Click to expand...

supposed
to

do
so), I got the following error message:

The SELECT statement includes a reserved word or an argument

Click to expand...

name
that

is
misspelled or missing, or the punctuation is incorrect.

But I still have not any clue where the problem is. Could you

Click to expand...

check
the

statement again to see where the problem is? Thank you very much.

Charles

:

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>

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" <[email protected]>
wrote in message
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.

.

Click to expand...

Click to expand...

Guest · Oct 13, 2004

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 said:
"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 said:

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

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>

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

:

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>

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>

message
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

:

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>

message
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" <[email protected]>
wrote in message
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.

.

Click to expand...

Click to expand...

Ken Snell [MVP] · Oct 13, 2004

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

"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 said:

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

Click to expand...

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

:

Either something is going wrong when you paste the SQL statement

Click to expand...

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

Click to expand...

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>

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

Click to expand...

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

:

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>

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

Click to expand...

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>

message
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

Click to expand...

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

Click to expand...

you
check

the
statement again to see where the problem is? Thank you very much.

Charles

:

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>

message
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" <[email protected]>
wrote in message
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.

.

Click to expand...

Click to expand...

Guest · Oct 14, 2004

Hi Ken:

Sorry for having delayed to get back to you. I have been busy with another
project and haven't got a chance to check this news group.

I copied your statement to SQL screen again but got the same error message.
The statement on the screen:
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;

The error message:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation isincorrect.

Sorry again to have bothered you some many times. I really appreciate your
help. Thanks a lot again.

Charles

Ken Snell said:
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 said:

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

"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>

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

:

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>

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

:

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>

message
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>

message
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

:

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>

message
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" <[email protected]>
wrote in message
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.

.

Click to expand...

Click to expand...

Ken Snell [MVP] · Oct 14, 2004

Ok - it does help when one is looking at something from someone else, and
not what I posted... < g >

I think I see the problem...it's an inadvertent comma just before FROM. Try
this one (sorry for the numerous "back and forth" posts!):

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>

Charles said:
Hi Ken:

Sorry for having delayed to get back to you. I have been busy with another
project and haven't got a chance to check this news group.

I copied your statement to SQL screen again but got the same error message.
The statement on the screen:
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;

The error message:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation isincorrect.

Sorry again to have bothered you some many times. I really appreciate your
help. Thanks a lot again.

Charles

Ken Snell said:

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

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

Click to expand...

any
way

you could test on your Access program to see if there is anything I

Click to expand...

did
not

follow correctly? Thank you so much.

Charles

:

"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>

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

Click to expand...

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

:

Either something is going wrong when you paste the SQL statement into
your
query's window, or something about your setup is different from

Click to expand...

what
I'm

understanding.

Post here the exact statement that your query says has an error

Click to expand...

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>

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

:

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>

message
No, don't copy to field row. Create a new query, don't

Click to expand...

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 (e-mail address removed)>

Click to expand...

wrote
in

message
Hi Ken:

Thank you so much for this subquery. But it still does

Click to expand...

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

Click to expand...

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.

Click to expand...

Could
you

check
the
statement again to see where the problem is? Thank you very
much.

Charles

:

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" <[email protected]>

Click to expand...

wrote
in

message
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"

Click to expand...

wrote in message
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

Click to expand...

query
M1,

M2, and M3. M1 is mean of G1, M2 is mean of G2,

Click to expand...

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

Click to expand...

type
in?

Thanks a lot.

Charles

I need to create a new field in the query.

.

Click to expand...

Click to expand...

Guest · Oct 14, 2004

Hi Ken:

When I used this expression to the real data base (about 5,300 records), it
took so long time to open the query. Is there any way we can have this code
run faster? Thanks.

Charles

Ken Snell said:
Ok - it does help when one is looking at something from someone else, and
not what I posted... < g >

I think I see the problem...it's an inadvertent comma just before FROM. Try
this one (sorry for the numerous "back and forth" posts!):

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>

Charles said:

Hi Ken:

Sorry for having delayed to get back to you. I have been busy with another
project and haven't got a chance to check this news group.

I copied your statement to SQL screen again but got the same error message.
The statement on the screen:
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;

The error message:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation isincorrect.

Sorry again to have bothered you some many times. I really appreciate your
help. Thanks a lot again.

Charles

Ken Snell said:

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>

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

:

"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>

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

:

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>

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

:

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>

message
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>

in
message
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

:

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>

in
message
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"

Click to expand...

wrote in message
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.

.

Click to expand...

Click to expand...

Ken Snell [MVP] · Oct 14, 2004

You could create a separate query:

SELECT T.Dept, Avg(T.G1) AS M1,
Avg(T.G2) AS M2, Avg(T.G3) AS M3
FROM Table1 AS T
GROUP BY T.Dept;

Name the above query qryAverages.

Then use this query in place of the one that is slow:

SELECT Table1.Dept, Table1.G1, Table1.G2,
Table1.G3, T.M1, T.M2, T.M3
FROM Table1 INNER JOIN qryAverages AS T
ON Table1.Dept = T.Dept
ORDER BY Table1.Dept;

--

Ken Snell
<MS ACCESS MVP>

Charles said:
Hi Ken:

When I used this expression to the real data base (about 5,300 records), it
took so long time to open the query. Is there any way we can have this code
run faster? Thanks.

Charles

Ken Snell said:

Ok - it does help when one is looking at something from someone else, and
not what I posted... < g >

I think I see the problem...it's an inadvertent comma just before FROM. Try
this one (sorry for the numerous "back and forth" posts!):

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>

the
SQL New,
then But
an that
is there
any I
did select
any again
this not
prior checked
the It
seems from
what error
in problem
still have
been wrote
in (e-mail address removed)>
wrote does
not not
able know I
am you
very

Click to expand...

wrote
in
message
Hi Ken:

Thanks for your interest in this:

SELECT Table1.Dept, Table1.G1, Table1.G2,

Click to expand...

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"

Click to expand...

wrote in message
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

Click to expand...

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:

Click to expand...

[Dept]
in

Total.
But the access does not accept. What is

Click to expand...

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.

.

Click to expand...

Click to expand...

Guest · Oct 15, 2004

Hi Ken:

Thanks for your continuing helps. Actually what I need to do is add top
quarter value to an existant report. This is the reason I need to calculate
the mean of raw data grouped by Dept. Then I need to use formula 75th
percentile=(Sum([M1])/Abs(Sum([M1]>0))+ 0.674*(Stdv(IIf([M1]=0,Null,[M1])) (I
tried to exclude zeros). After added with this formula, it takes a long time
to run the report. The original report includes Percentage, Average.

If I create a separate query, can I use two queries for the same report? If
can, could you tell me how I can hook up two queries to ONE report?
Sincerely thank you very much.

Charles

Ken Snell said:
You could create a separate query:

SELECT T.Dept, Avg(T.G1) AS M1,
Avg(T.G2) AS M2, Avg(T.G3) AS M3
FROM Table1 AS T
GROUP BY T.Dept;

Name the above query qryAverages.

Then use this query in place of the one that is slow:

SELECT Table1.Dept, Table1.G1, Table1.G2,
Table1.G3, T.M1, T.M2, T.M3
FROM Table1 INNER JOIN qryAverages AS T
ON Table1.Dept = T.Dept
ORDER BY Table1.Dept;

--

Ken Snell
<MS ACCESS MVP>

Charles said:

Hi Ken:

When I used this expression to the real data base (about 5,300 records), it
took so long time to open the query. Is there any way we can have this code
run faster? Thanks.

Charles

Ken Snell said:

Ok - it does help when one is looking at something from someone else, and
not what I posted... < g >

I think I see the problem...it's an inadvertent comma just before FROM. Try
this one (sorry for the numerous "back and forth" posts!):

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>

Hi Ken:

Sorry for having delayed to get back to you. I have been busy with another
project and haven't got a chance to check this news group.

I copied your statement to SQL screen again but got the same error
message.
The statement on the screen:
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;

The error message:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation isincorrect.

Sorry again to have bothered you some many times. I really appreciate your
help. Thanks a lot again.

Charles

:

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>

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

:

"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>

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

:

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>

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

:

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>

in
message
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 (e-mail address removed)>
wrote
in
message
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

:

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"

Click to expand...

wrote
in
message
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"
<[email protected]>
wrote in message
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.

.

Click to expand...

Click to expand...

Calculated Mean

Charles Deng

Ken Snell [MVP]

Charles Deng

Ken Snell [MVP]

Guest

Guest

Ken Snell [MVP]

Ken Snell [MVP]

Guest

Ken Snell [MVP]

Guest

Ken Snell [MVP]

Ken Snell [MVP]

Guest

Ken Snell [MVP]

Guest

Ken Snell [MVP]

Guest

Ken Snell [MVP]

Guest