By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,369 Members | 1,188 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,369 IT Pros & Developers. It's quick & easy.

Standard Deviations in Microsoft Access

P: 3
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...
Oct 25 '07 #1
Share this Question
Share on Google+
9 Replies


puppydogbuddy
Expert 100+
P: 1,923
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
Oct 25 '07 #2

P: 3
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?
Oct 25 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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
Oct 25 '07 #4

P: 3
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.
Oct 25 '07 #5

FishVal
Expert 2.5K+
P: 2,653
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.
Oct 25 '07 #6

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 26 '07 #7

ADezii
Expert 5K+
P: 8,669
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:

    Expand|Select|Wrap|Line Numbers
    1. Public Function RStDev(ParamArray FieldValues()) As Variant
    2.  
    3. '---------------------------------------------------------
    4.  
    5. ' Function RStDev() calculates the Standard Deviation of
    6.  
    7. ' sample data passed as arguments. NOTE: The standard deviation
    8.  
    9. ' of sample data is only valid if more than one argument is
    10.  
    11. ' numeric.
    12.  
    13. '---------------------------------------------------------
    14.  
    15. Dim dblSum As Double, dblSumOfSq As Double
    16.  
    17. Dim n As Long, varArg As Variant
    18.  
    19. For Each varArg In FieldValues
    20.  
    21.   If IsNumeric(varArg) Then
    22.  
    23.     dblSum = dblSum + varArg
    24.  
    25.     dblSumOfSq = dblSumOfSq + varArg * varArg
    26.  
    27.       n = n + 1
    28.  
    29.   End If
    30.  
    31. Next
    32.  
    33.  
    34.  
    35. If n > 1 Then ' Variance/StDev applies if more than a single point
    36.  
    37.   RStDev = Sqr((n * dblSumOfSq - dblSum * dblSum) / (n * (n - 1)))
    38.  
    39. Else
    40.  
    41.   RStDev = Null
    42.  
    43. End If
    44.  
    45. 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].

    Expand|Select|Wrap|Line Numbers
    1. SELECT Val(RStDev([Field1],[Field2],[Field3],[Field4],[Field5],[Field6], [Field7],[Field8],[Field9],[Field10],[Field11],[Field12])) AS Std_Deviation
    2.  
    3. 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.
Oct 26 '07 #8

puppydogbuddy
Expert 100+
P: 1,923
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
Oct 26 '07 #9

ADezii
Expert 5K+
P: 8,669
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.
Oct 26 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.