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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#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
|
|||
|
|||
#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 | |
|
|