Standard Deviations in Microsoft Access

[lajohn63]

I'm trying to calculate a standard deviation for each record in a database...the record has 12 fields which contain consumption for each month of the year. I want to calculate the standard deviation of those 12 fields but it seems like access is wanting to calculate only on one field...
Is there any way around this or am I just not understanding how it works?
I'm fairly new to access and don't know anything about VB and some of the sql statements...

[puppydogbuddy]

I'm trying to calculate a standard deviation for each record in a database...the record has 12 fields which contain consumption for each month of the year. I want to calculate the standard deviation of those 12 fields but it seems like access is wanting to calculate only on one field...
Is there any way around this or am I just not understanding how it works?
I'm fairly new to access and don't know anything about VB and some of the sql statements...

The following link should enable you to solve your problem:

http://support.microsoft.com/kb/209839

[lajohn63]

The following link should enable you to solve your problem:

http://support.microsoft.com/kb/209839

Thank you for the response but VB is not something I've ever used. Do I just cut-n-paste the module code into my database? Or do I need to change something in it's coding?

[FishVal]

I'm trying to calculate a standard deviation for each record in a database...the record has 12 fields which contain consumption for each month of the year. I want to calculate the standard deviation of those 12 fields but it seems like access is wanting to calculate only on one field...
Is there any way around this or am I just not understanding how it works?
I'm fairly new to access and don't know anything about VB and some of the sql statements...

Hi, there.

You have two options:

• within a query you may use StDev or StDevP aggregate functions
• in VBA you may use DStDev or DStDevP domain aggregate functions

[lajohn63]

Hi, there.

You have two options:

• within a query you may use StDev or StDevP aggregate functions
• in VBA you may use DStDev or DStDevP domain aggregate functions

The StDev or StDevP seems to only work on one field within a record where I want to look at 12 fields which are by month. I attempted to use it but it won't recognize more than 1 field....or so it seems.

Example: I have a material number and then usage for Jan, Feb, Mar, Apr, May, thru Dec. I want to find the Std Deviation for those 12 months of data.

[FishVal]

The StDev or StDevP seems to only work on one field within a record where I want to look at 12 fields which are by month. I attempted to use it but it won't recognize more than 1 field....or so it seems.

Example: I have a material number and then usage for Jan, Feb, Mar, Apr, May, thru Dec. I want to find the Std Deviation for those 12 months of data.

That is because you have an improper table structure. Month usage should be in different records, not in different fields.
Take a look at Database Normalisation and Table structures tutorial.

If you are not willing to reorganize your tables structure, then read this stuff.

[puppydogbuddy]

Thank you for the response but VB is not something I've ever used. Do I just cut-n-paste the module code into my database? Or do I need to change something in it's coding?

FishVal's comments about normalization are valid, and should be implemented in your database. However, I believe the functions included in the link will enable you to do the computations you want using your existing database structure, although I have not personally used them.

Just cut-n-paste the module code in your database, and compile it. then you will be able to call the included functions and pass arrays consisting of your column(field) values. The included functions utilize the statistical function options mentioned by Fish. Hope this helps.

[ADezii]

I'm trying to calculate a standard deviation for each record in a database...the record has 12 fields which contain consumption for each month of the year. I want to calculate the standard deviation of those 12 fields but it seems like access is wanting to calculate only on one field...
Is there any way around this or am I just not understanding how it works?
I'm fairly new to access and don't know anything about VB and some of the sql statements...

1. Copy and Paste the following Function to a Standard Code Module:
   
   

   Public Function RStDev(ParamArray FieldValues()) As Variant
    
   '---------------------------------------------------------
    
   ' Function RStDev() calculates the Standard Deviation of
    
   ' sample data passed as arguments. NOTE: The standard deviation
    
   ' of sample data is only valid if more than one argument is
    
   ' numeric.
    
   '---------------------------------------------------------
    
   Dim dblSum As Double, dblSumOfSq As Double
    
   Dim n As Long, varArg As Variant
    
   For Each varArg In FieldValues
    
     If IsNumeric(varArg) Then
    
       dblSum = dblSum + varArg
    
       dblSumOfSq = dblSumOfSq + varArg * varArg
    
         n = n + 1
    
     End If
    
   Next
    
    
    
   If n > 1 Then ' Variance/StDev applies if more than a single point
    
     RStDev = Sqr((n * dblSumOfSq - dblSum * dblSum) / (n * (n - 1)))
    
   Else
    
     RStDev = Null
    
   End If
    
   End Function

2. Make you own modifications, but for test purposes I created a Table named tblStandardDeviation.
3. tblStandardDeviation consists of an [ID] Field (Primary Key){AutoNumber}, and 12 Fields named [Field1] through [Field12]. Set the Data Type of these Fields to Double.
4. Create the following Query, substituting your Field Names for the generic [Field1] through [Field12].
   
   

   SELECT Val(RStDev([Field1],[Field2],[Field3],[Field4],[Field5],[Field6], [Field7],[Field8],[Field9],[Field10],[Field11],[Field12])) AS Std_Deviation
    
   FROM tblStandardDeviation;

5. I do believe that you will arrive at the correct results.
6. Please give puppydogbuddy the credit for this one, he pointed you in the right direction with the proper Link. I just followed in his footsteps and implemented what he had already found.

NOTE: As indicated in the Link, Microsoft Access has several built-in functions that enable you to perform statistical analysis across Records, it does not have a built-in function to perform statistical analysis across multiple columns within a single row. This is why the need for a Custom Function to perform this task.

[puppydogbuddy]

ADezii,
Thanks for confirming the validity and relevance of info that I provided, and for the excellent job you did of filling in the missing detail that I was not familiar with.

pDog

[ADezii]

ADezii,
Thanks for confirming the validity and relevance of info that I provided, and for the excellent job you did of filling in the missing detail that I was not familiar with.

pDog

Always a pleasure working with a fellow Expert.