Standard Deviation

  • Thread starter Thread starter Charles Deng
  • Start date Start date
C

Charles Deng

Hi All:

I tried to post standard deviation on my report but the
following syntax does not work (Report shows #error.).
Could anyone do my a favour to check what is wrong with my
syntax:

=DStDev("[q1]", "SLA S-04 Query 3", "[q1]>0")

Where q1 is variable name in SLA S-04 Query 3 which is the
query my report is based. All records in q1 are numberic
and I successfully reported average of q1. By the way my
report is sorted by Department which is not shown in the
syntax.

Any suggestion and advice will be highly appreciated.
Thanks.

Chalres
 
Hi Roger:

You are right. The syntax in my previous message is
correct. Actually I tried to goup the data before the
standard deviation is calculated. But when I use StDev or
StDevP, I got an error message:

"The expression you entered has function containing the
wrong number of arguments".

Is there any problem with my Access installation or the
syntax StDev or StDevP does not work? Your response or
any suggestion will be very appreciated.

Charles


-----Original Message-----
As far as I can tell, it has nothing to do with your syntax. It works
perfectly for me. Something else is going on. Look farther afield.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe? SUBED1=ACCESS-L

Hi All:

I tried to post standard deviation on my report but the
following syntax does not work (Report shows #error.).
Could anyone do my a favour to check what is wrong with my
syntax:

=DStDev("[q1]", "SLA S-04 Query 3", "[q1]>0")

Where q1 is variable name in SLA S-04 Query 3 which is the
query my report is based. All records in q1 are numberic
and I successfully reported average of q1. By the way my
report is sorted by Department which is not shown in the
syntax.

Any suggestion and advice will be highly appreciated.
Thanks.

Chalres
Click to expand...


.
Click to expand...
 
I really don't know. I would look at the query "SLA S-04 Query 3" to see if
something there is causing the problem. Perhaps there is something in the
query the DStdDev function does not like it.

But now you've got me confused. Are you using StDev in an aggregate query
or DStDev on a report?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi Roger:

You are right. The syntax in my previous message is
correct. Actually I tried to goup the data before the
standard deviation is calculated. But when I use StDev or
StDevP, I got an error message:

"The expression you entered has function containing the
wrong number of arguments".

Is there any problem with my Access installation or the
syntax StDev or StDevP does not work? Your response or
any suggestion will be very appreciated.

Charles


-----Original Message-----
As far as I can tell, it has nothing to do with your syntax. It works
perfectly for me. Something else is going on. Look farther afield.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe? SUBED1=ACCESS-L

Hi All:

I tried to post standard deviation on my report but the
following syntax does not work (Report shows #error.).
Could anyone do my a favour to check what is wrong with my
syntax:

=DStDev("[q1]", "SLA S-04 Query 3", "[q1]>0")

Where q1 is variable name in SLA S-04 Query 3 which is the
query my report is based. All records in q1 are numberic
and I successfully reported average of q1. By the way my
report is sorted by Department which is not shown in the
syntax.

Any suggestion and advice will be highly appreciated.
Thanks.

Chalres
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Roger:

I need to use StDev in an aggregate query. My report is
gouped by Department and I want to report Average and
Standard Deviation on Department Report, that means the
query will be grouped by Department and each Department
has its Report. But when I change DStDev to StDev, I got
an error message: "The expression you entered has function
containing the wrong number of arguments". Can you figure
out what the problem is based on this message? I am
suspecious that my access may not have this function
because I tried to access help for StDev, I got an error
message:"An error occurred and this feature is no longer
functioning properly. Would you like to repair this
feature now?" But when I clicked OK, my computer
generated another error message:"Microsoft access cannot
install the necessary files due to windows installer error
1603. Fatal error during installation."

Any ideas about these problems? I appreciate you responses.
Thanks.


Charles


-----Original Message-----
I really don't know. I would look at the query "SLA S-04 Query 3" to see if
something there is causing the problem. Perhaps there is something in the
query the DStdDev function does not like it.

But now you've got me confused. Are you using StDev in an aggregate query
or DStDev on a report?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe? SUBED1=ACCESS-L

Hi Roger:

You are right. The syntax in my previous message is
correct. Actually I tried to goup the data before the
standard deviation is calculated. But when I use StDev or
StDevP, I got an error message:

"The expression you entered has function containing the
wrong number of arguments".

Is there any problem with my Access installation or the
syntax StDev or StDevP does not work? Your response or
any suggestion will be very appreciated.

Charles


-----Original Message-----
As far as I can tell, it has nothing to do with your syntax. It works
perfectly for me. Something else is going on. Look farther afield.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe? SUBED1=ACCESS-L

Hi All:

I tried to post standard deviation on my report but the
following syntax does not work (Report shows #error.).
Could anyone do my a favour to check what is wrong
Click to expand...
with
my
syntax:

=DStDev("[q1]", "SLA S-04 Query 3", "[q1]>0")

Where q1 is variable name in SLA S-04 Query 3 which
Click to expand...
is
the
query my report is based. All records in q1 are numberic
and I successfully reported average of q1. By the
Click to expand...
way
my
report is sorted by Department which is not shown in the
syntax.

Any suggestion and advice will be highly appreciated.
Thanks.

Chalres





.
Click to expand...
Click to expand...


.
Click to expand...
 
DStdDev and StdDev are very different functions.

DStdDev is a domain aggregate function, sort of an Access specific subquery
(sort of). For that, you must give the field, Table, and a criteria.

StdDev can be used in an aggregate query, or (as in your case) a report in a
*Group footer* or *Report footer*. It CANNOT be in a *Page* footer. You
will get an #Error value in it. The syntax for it would be: =StDev([q1])


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi Roger:

I need to use StDev in an aggregate query. My report is
gouped by Department and I want to report Average and
Standard Deviation on Department Report, that means the
query will be grouped by Department and each Department
has its Report. But when I change DStDev to StDev, I got
an error message: "The expression you entered has function
containing the wrong number of arguments". Can you figure
out what the problem is based on this message? I am
suspecious that my access may not have this function
because I tried to access help for StDev, I got an error
message:"An error occurred and this feature is no longer
functioning properly. Would you like to repair this
feature now?" But when I clicked OK, my computer
generated another error message:"Microsoft access cannot
install the necessary files due to windows installer error
1603. Fatal error during installation."

Any ideas about these problems? I appreciate you responses.
Thanks.


Charles


-----Original Message-----
I really don't know. I would look at the query "SLA S-04 Query 3" to see if
something there is causing the problem. Perhaps there is something in the
query the DStdDev function does not like it.

But now you've got me confused. Are you using StDev in an aggregate query
or DStDev on a report?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe? SUBED1=ACCESS-L

Hi Roger:

You are right. The syntax in my previous message is
correct. Actually I tried to goup the data before the
standard deviation is calculated. But when I use StDev or
StDevP, I got an error message:

"The expression you entered has function containing the
wrong number of arguments".

Is there any problem with my Access installation or the
syntax StDev or StDevP does not work? Your response or
any suggestion will be very appreciated.

Charles



-----Original Message-----
As far as I can tell, it has nothing to do with your
syntax. It works
perfectly for me. Something else is going on. Look
farther afield.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?
SUBED1=ACCESS-L

"Charles Deng" <[email protected]>
wrote in message
Hi All:

I tried to post standard deviation on my report but the
following syntax does not work (Report shows #error.).
Could anyone do my a favour to check what is wrong with
my
syntax:

=DStDev("[q1]", "SLA S-04 Query 3", "[q1]>0")

Where q1 is variable name in SLA S-04 Query 3 which is
the
query my report is based. All records in q1 are
numberic
and I successfully reported average of q1. By the way
my
report is sorted by Department which is not shown in the
syntax.

Any suggestion and advice will be highly appreciated.
Thanks.

Chalres





.
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Roger:

I very appreciate your help and StdDev does work when I
moved control source box to *Group Header*. But I would
rather it works in *Group footer* because I have already
put all averages and percentages in *Group footer*. Is
there any way I can fix this problem? I would like to
allign Standard Deviation and Average on the same
horizontal level.

The second problem is that why the value of standard
deviation calculated this way is so big. For the following
example query:

dept q1 q2 q3
ACC 2.23 3.32 2.14
ACC 3.55 2.65 3.85
ACC 1.98 1.52 2.34
ACC 2.55 2.15 2.87
BUS 2.68 3.54 1.95
BUS 1.25 3.65 1.45
BUS 2.65 2.64 3.54
BUS 2.58 2.51 1.08

(The following discussion is only for q1)

Standard Deviation (StDev) of ACC group is 0.689 and
Standard Deviation (StDev) of BUS group is 0.695.

While Standard Deviation (DStdev: calculated before data
was grouped) for the whole data set is 0.66.

While the mean for ACC group is 2.58 and mean for BUS
group is 2.29.

If we transfer these standard deviations to percentiles,
we can find these deviations are little bit way off.

For 75th percentile, the z-table value is

0.674

If we time the standard deviation of group BUS (0.695) by
this value, we will obtain 75th percentile as

2.29 + 0.695*0.674 = 2.76

But if you check all the records in BUS group, the maxmum
is 2.68. How come its 75th percentile is 2.76. I am
really confused about this result.

Could you tell me if there is anything wrong with my
calculation or there is error in syntax I used? I have
been thinking about this for the past couple of days. If
you can help me with this, I will very very thank you.

Charles







-----Original Message-----
DStdDev and StdDev are very different functions.

DStdDev is a domain aggregate function, sort of an Access specific subquery
(sort of). For that, you must give the field, Table, and a criteria.

StdDev can be used in an aggregate query, or (as in your case) a report in a
*Group footer* or *Report footer*. It CANNOT be in a *Page* footer. You
will get an #Error value in it. The syntax for it would be: =StDev([q1])


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe? SUBED1=ACCESS-L


Hi Roger:

I need to use StDev in an aggregate query. My report is
gouped by Department and I want to report Average and
Standard Deviation on Department Report, that means the
query will be grouped by Department and each Department
has its Report. But when I change DStDev to StDev, I got
an error message: "The expression you entered has function
containing the wrong number of arguments". Can you figure
out what the problem is based on this message? I am
suspecious that my access may not have this function
because I tried to access help for StDev, I got an error
message:"An error occurred and this feature is no longer
functioning properly. Would you like to repair this
feature now?" But when I clicked OK, my computer
generated another error message:"Microsoft access cannot
install the necessary files due to windows installer error
1603. Fatal error during installation."

Any ideas about these problems? I appreciate you responses.
Thanks.


Charles


-----Original Message-----
I really don't know. I would look at the query "SLA S-
Click to expand...
04
Query 3" to see if
something there is causing the problem. Perhaps there is something in the
query the DStdDev function does not like it.

But now you've got me confused. Are you using StDev in an aggregate query
or DStDev on a report?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe? SUBED1=ACCESS-L

Hi Roger:

You are right. The syntax in my previous message is
correct. Actually I tried to goup the data before the
standard deviation is calculated. But when I use
Click to expand...
StDev
or
StDevP, I got an error message:

"The expression you entered has function containing the
wrong number of arguments".

Is there any problem with my Access installation or the
syntax StDev or StDevP does not work? Your response or
any suggestion will be very appreciated.

Charles



-----Original Message-----
As far as I can tell, it has nothing to do with your
syntax. It works
perfectly for me. Something else is going on. Look
farther afield.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?
SUBED1=ACCESS-L

"Charles Deng" <[email protected]>
wrote in message
Hi All:

I tried to post standard deviation on my report
Click to expand...
but
the
following syntax does not work (Report shows #error.).
Could anyone do my a favour to check what is wrong with
my
syntax:

=DStDev("[q1]", "SLA S-04 Query 3", "[q1]>0")

Where q1 is variable name in SLA S-04 Query 3
Click to expand...
which
is
the
query my report is based. All records in q1 are
numberic
and I successfully reported average of q1. By the way
my
report is sorted by Department which is not shown
Click to expand...
in
the
syntax.

Any suggestion and advice will be highly appreciated.
Thanks.

Chalres





.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Sorry for the delay in responding.

Are you certain you are putting it in the Group Footer? If it works in the
Group Header, it should work in the Group Footer. Make certain it is NOT
the Page Footer, because it will not work there. To make sure there is a
group footer, go to Sorting and Grouping on the menu and make sure there is
a YES in both the Group Header property and Group Footer property.


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi Roger:

I very appreciate your help and StdDev does work when I
moved control source box to *Group Header*. But I would
rather it works in *Group footer* because I have already
put all averages and percentages in *Group footer*. Is
there any way I can fix this problem? I would like to
allign Standard Deviation and Average on the same
horizontal level.

The second problem is that why the value of standard
deviation calculated this way is so big. For the following
example query:

dept q1 q2 q3
ACC 2.23 3.32 2.14
ACC 3.55 2.65 3.85
ACC 1.98 1.52 2.34
ACC 2.55 2.15 2.87
BUS 2.68 3.54 1.95
BUS 1.25 3.65 1.45
BUS 2.65 2.64 3.54
BUS 2.58 2.51 1.08

(The following discussion is only for q1)

Standard Deviation (StDev) of ACC group is 0.689 and
Standard Deviation (StDev) of BUS group is 0.695.

While Standard Deviation (DStdev: calculated before data
was grouped) for the whole data set is 0.66.

While the mean for ACC group is 2.58 and mean for BUS
group is 2.29.

If we transfer these standard deviations to percentiles,
we can find these deviations are little bit way off.

For 75th percentile, the z-table value is

0.674

If we time the standard deviation of group BUS (0.695) by
this value, we will obtain 75th percentile as

2.29 + 0.695*0.674 = 2.76

But if you check all the records in BUS group, the maxmum
is 2.68. How come its 75th percentile is 2.76. I am
really confused about this result.

Could you tell me if there is anything wrong with my
calculation or there is error in syntax I used? I have
been thinking about this for the past couple of days. If
you can help me with this, I will very very thank you.

Charles







-----Original Message-----
DStdDev and StdDev are very different functions.

DStdDev is a domain aggregate function, sort of an Access specific subquery
(sort of). For that, you must give the field, Table, and a criteria.

StdDev can be used in an aggregate query, or (as in your case) a report in a
*Group footer* or *Report footer*. It CANNOT be in a *Page* footer. You
will get an #Error value in it. The syntax for it would be: =StDev([q1])


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe? SUBED1=ACCESS-L


Hi Roger:

I need to use StDev in an aggregate query. My report is
gouped by Department and I want to report Average and
Standard Deviation on Department Report, that means the
query will be grouped by Department and each Department
has its Report. But when I change DStDev to StDev, I got
an error message: "The expression you entered has function
containing the wrong number of arguments". Can you figure
out what the problem is based on this message? I am
suspecious that my access may not have this function
because I tried to access help for StDev, I got an error
message:"An error occurred and this feature is no longer
functioning properly. Would you like to repair this
feature now?" But when I clicked OK, my computer
generated another error message:"Microsoft access cannot
install the necessary files due to windows installer error
1603. Fatal error during installation."

Any ideas about these problems? I appreciate you responses.
Thanks.


Charles



-----Original Message-----
I really don't know. I would look at the query "SLA S- 04
Query 3" to see if
something there is causing the problem. Perhaps there
is something in the
query the DStdDev function does not like it.

But now you've got me confused. Are you using StDev in
an aggregate query
or DStDev on a report?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?
SUBED1=ACCESS-L

Hi Roger:

You are right. The syntax in my previous message is
correct. Actually I tried to goup the data before the
standard deviation is calculated. But when I use StDev
or
StDevP, I got an error message:

"The expression you entered has function containing the
wrong number of arguments".

Is there any problem with my Access installation or the
syntax StDev or StDevP does not work? Your response or
any suggestion will be very appreciated.

Charles



-----Original Message-----
As far as I can tell, it has nothing to do with your
syntax. It works
perfectly for me. Something else is going on. Look
farther afield.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?
SUBED1=ACCESS-L

"Charles Deng" <[email protected]>
wrote in message
Hi All:

I tried to post standard deviation on my report but
the
following syntax does not work (Report shows
#error.).
Could anyone do my a favour to check what is wrong
with
my
syntax:

=DStDev("[q1]", "SLA S-04 Query 3", "[q1]>0")

Where q1 is variable name in SLA S-04 Query 3 which
is
the
query my report is based. All records in q1 are
numberic
and I successfully reported average of q1. By the
way
my
report is sorted by Department which is not shown in
the
syntax.

Any suggestion and advice will be highly appreciated.
Thanks.

Chalres





.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top