#Error when using stDev in query

[noel_juaire]

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.

 

[Michel Walsh]

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