Calculated Mean

[Ken Snell [MVP]]

Running Stdv function is going to be slow, no matter what you do with the
queries. I think you'll just have to wait for the query to run its course.

--

Ken Snell
<MS ACCESS MVP>

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

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>

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




:

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

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.


.

 

[Guest]

Hi Ken:

You are right and probably we have to wait for its running. Thanks.

Charles

Running Stdv function is going to be slow, no matter what you do with the
queries. I think you'll just have to wait for the query to run its course.

--

Ken Snell
<MS ACCESS MVP>

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

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>

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




:

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>



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




"Ken Snell [MVP]" <[email protected]>
wrote
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"
<[email protected]>
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.


.