Standard Deviations query

[TJVFree]

I was assuming that this would be something I could find on the web, but I'm just not finding it I'm not.
How would I get I use a query to get the Standard Deviations?
From what I read it should be something like this, but this one isn’t working.
When I try this

Standard Deviations: (DStDev([Jan_Fund],[Feb_Fund],[Mar_Fund]))

I'm getting a reading in the SD field that shows "#Error"
So if I took all the months from Jan - Dec, what would the formula be for a Standard Deviation?

Standard Deviations: Val(StDev ([Jan_Fund],[Feb_Fund],[Mar_Fund],[Apr_Fund],[May_Fund],[Jun_Fund],[Jul_Fund],[A
ug_Fund],[Sep_Fund],[Oct_Fund],[Nov_Fund],[Dec_Fund],0)

or something like this:

(DStDev([Jan_Fund],[Feb_Fund],[Mar_Fund]))

or

(StDev([Jan_Fund]),([Feb_Fund],[Mar_Fund]))

Thank for all your help

TCB

 

[dhookom]

Your issue is your table structure. It is not normalized. "Jan" (actually the value 1) should be a data value in a field, not a part of a field name.

If you don't normalize your data, you will continue to be haunted by issues like this. If you can't or won't change the table structure, you can create a union query that normalizes your data and then use a totals query with StDev.

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

Duane - I always appreciate your comments and advice. Could you give me some main points that I should do first to start to normalize my data and or queries in my database?

 

[dhookom]

Google it

If you have questions after reading through some of these, please reply back in this thread with your current table structure.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

To support what Duane is saying here is what you would have to do to allow you to get the Standard dev across fields. All of this is done with one word if you normalize your table. But if you cannot, because you do not control the data then:

select ... getSDev([field1],[field2],...[fieldN]) as StandardDev ...

Public Function getSDev(ParamArray varVals() As Variant) As Variant
  Dim varVal As Variant
  Dim intcount As Integer
  Dim Arr() As Variant
  For Each varVal In varVals
    If IsNumeric(varVal) Then
      Arr = AddElement(Arr, varVal)
    End If
  Next varVal
  getSDev = StdDev(Arr)
End Function

And you will need all of this

Function StdDev(Arr() As Variant) As Variant
     Dim i As Integer
     Dim avg As Single, SumSq As Single
     Dim k As Integer
     avg = Mean(Arr)
     For i = LBound(Arr) To UBound(Arr)
          SumSq = SumSq + (Arr(i) - avg) ^ 2
          k = k + 1
     Next i
     StdDev = Sqr(SumSq / (k - 1))
End Function
Public Function AddElement(ByVal vArray As Variant, ByVal vElem As Variant) As Variant
      ' This function adds an element to a Variant array
      ' and returns an array with the element added to it.
      Dim vRet As Variant ' To be returned
      If IsEmpty(vArray) Or Not IsDimensioned(vArray) Then
          ' First time through, create an array of size 1.
          vRet = Array(vElem)
      Else
          vRet = vArray
          ' From then on, ReDim Preserve will work.
          ReDim Preserve vRet(UBound(vArray) + 1)
          vRet(UBound(vRet)) = vElem
      End If
      AddElement = vRet
  End Function
Public Function IsDimensioned(ByRef TheArray) As Boolean
      If IsArray(TheArray) Then ' we need to test it! otherwise will return false if not an array!
                      ' If you put extensive use to this function then you might modify
                      ' it a lil' bit so it "takes in" specific array type & you can skip IsArray
                      ' (currently you can pass any variable).
        On Error Resume Next
            IsDimensioned = ((UBound(TheArray) - LBound(TheArray)) >= 0)
        On Error GoTo 0
    Else
        'IsDimensioned = False ' is already false by default
        Call Err.Raise(5, "IsDimensioned", "Invalid procedure call or argument. Argument is not an array!")
    End If
End Function
Public Function HasDimension(ByRef TheArray, Optional ByRef Dimension As Long = 1) As Boolean
    Dim isDim As Boolean
    Dim ErrNumb As Long
    Dim LB As Long
    Dim errDesc As String
    'HasDimension = False
    
    If (Dimension > 60) Or (Dimension < 1) Then
        Call Err.Raise(9, "HasDimension", "Subscript out of range. ""Dimension"" parameter is not in its legal borders (1 to 60)! Passed dimension value is: " & Dimension)
        Exit Function
    End If
    
    On Error Resume Next
        isDim = IsDimensioned(TheArray) 'IsArray & IsDimensioned in one call. If Err 5 will be generated if not Array
        ErrNumb = Err.Number
        If ErrNumb <> 0 Then
            errDesc = Err.Description
        End If
    On Error GoTo 0
    
    Select Case ErrNumb
        Case 0
            If isDim Then
                On Error Resume Next
                    LB = LBound(TheArray, Dimension) 'just try to retrive Lbound
                    HasDimension = (Err.Number = 0)
                On Error GoTo 0
            End If
        Case 5
            Call Err.Raise(5, "HasDimension", "Invalid procedure call or argument. Argument is not an array!")
        Case Else
            Call Err.Raise(vbObjectError + 1, "HasDimension", _
                "This is unexpected error, caused when calling ""IsDimensioned"" function!" & vbCrLf & _
                "Original error: " & ErrNumb & vbCrLf & _
                "Description:" & errDesc)
    End Select
End Function
Function Mean(Arr() As Variant)
     Dim Sum As Single
     Dim i As Integer
     Dim k As Integer
     Sum = 0
     For i = LBound(Arr) To UBound(Arr)
         k = k + 1
         Sum = Sum + Arr(i)
     Next i
 
     Mean = Sum / k
     'MsgBox Mean
End Function

 

[TJVFree]

Thanks MajP, I appreciate all your help. and look forward to seeing what you post to help my issues.

The code you provide just gives me this little error below:

 IsDimensioned = ((UBound(TheArray) - LBound(TheArray)) >= 0)

Thanks again for all your help
TCB