A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

#Error when using stDev in query



 
 
Thread Tools Display Modes
  #1  
Old May 4th, 2007, 07:33 AM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 1
Default #Error when using stDev in query

I am getting an error when running a query with StDev in Access. Most
rows have a good calc value, but 2 rows have an #Error value. When I
try to sort by the StDev column I get an overflow error, I also get
overflow errors when I try to run a query based on this query. The
same things happen when I use the var function. The data in the
column has no null values and is a valid numeric data type (currency).

Here is the query:
SELECT Code, Count(t1.acct) AS CountOfAcct, Min(t1.totalchgs) AS
MinOftotalchgs, Max(t1.totalchgs) AS MaxOftotalchgs,
Avg(Bhtn_ipvol.totalchgs) AS AvgOftotalchgs, stdev(t1.totalchgs) AS
StDevOftotalchgs
FROM t1
GROUP BY Code


Here is a snippet of the data:
Code CountOfacct MinOftotalchgs MaxOftotalchgs AvgOftotalchgs
StDevOftotalchgs
372 613 $3,064.40 $64,121.21 $10,186.93 5274.24
373 3422 $1,547.35 $99,172.31 $8,266.91 #Error
374 45 $4,480.93 $27,785.28 $11,476.62 4729.02


If I calculate the StDev manually, I do not get an error, but it takes
a VERY LONG time to complete the query.

Any insight would be helpful.

  #2  
Old May 7th, 2007, 07:36 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default #Error when using stDev in query

If memory serves, stDev involves a division by n-1, n being the number of
records. So, if there is just one record in the group, the standard
deviation is not defined (since the whole sampling of one record is already
used to approximate the mean, and that we cannot derive TWO different
information, the mean and the deviation, where there is just ONE present,
initially, ... we are doomed in the case a group has only one record, as per
getting its deviation is required).


Hoping it may help,
Vanderghast, Access MVP



wrote in message
oups.com...
I am getting an error when running a query with StDev in Access. Most
rows have a good calc value, but 2 rows have an #Error value. When I
try to sort by the StDev column I get an overflow error, I also get
overflow errors when I try to run a query based on this query. The
same things happen when I use the var function. The data in the
column has no null values and is a valid numeric data type (currency).

Here is the query:
SELECT Code, Count(t1.acct) AS CountOfAcct, Min(t1.totalchgs) AS
MinOftotalchgs, Max(t1.totalchgs) AS MaxOftotalchgs,
Avg(Bhtn_ipvol.totalchgs) AS AvgOftotalchgs, stdev(t1.totalchgs) AS
StDevOftotalchgs
FROM t1
GROUP BY Code


Here is a snippet of the data:
Code CountOfacct MinOftotalchgs MaxOftotalchgs AvgOftotalchgs
StDevOftotalchgs
372 613 $3,064.40 $64,121.21 $10,186.93 5274.24
373 3422 $1,547.35 $99,172.31 $8,266.91 #Error
374 45 $4,480.93 $27,785.28 $11,476.62 4729.02


If I calculate the StDev manually, I do not get an error, but it takes
a VERY LONG time to complete the query.

Any insight would be helpful.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.