Calculated Mean

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.

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

--

Ken Snell
MS ACCESS MVP

"Charles Deng" 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.

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


.

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


.

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


.

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




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


.

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


.