Functie STDEV in expresion builder werkt niet over meerdere kolommen?

[Deef1290]

Binnen mijn query heb ik meerdere kolommen met verbruik per periode. Nu wil ik graag de functie STDEV toepassen.

In de query wil ik graag een nieuwe kolom toevoegen waarbij hij per regel de STDEV moet berekenen, deze moet hij gaan berekenen over de kolommen E t/m M uit mijn excel voorbeeld. Ik heb geen kennis van SQL. Weet iemand hiervoor een oplossing?
Gr. Dave

 

[OctaFish]

Je kunt eens op deze pagina van Microsoft kijken voor een voorbeeld van een functie.

 

[OctaFish]

Of deze functie plakken in een nieuwe module ....

Public Function RStDev(ParamArray FieldValues()) As Variant
  
'---------------------------------------------------------
'Voorbeeld:
'SELECT Val(RStDev([Field1],[Field2],[Field3],[Field4],...,[Field11],[Field12])) AS Std_Deviation FROM tblStandardDeviation;

' 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

 

[Deef1290]

Hoi Octafish,

Bedankt voor je hulp, maar het wordt er niet makkelijker op. Zoals ik al een beetje schreef heb ik geen ervaring in sql en merk nu dus ook geen ervaring met modules. Ik had gehoopt dat er een (voor mij) makkelijkere oplossing zou zijn. Ben ondertussen een andere benadering aan het proberen.

Eerst heb ik een nieuwe kolom gemaakt met een gemiddeld verbruik: Average: [Sum Usage]/9

Van de 9 maanden waar ik verbruik heb gehad heb ik nieuwe kolommen gemaaktvoorbeeld van 1 maand) Abs dev1: nz(Abs([2009-08]-[Average])^2,0)

Daarna volgt er weer een nieuwe kolom waarbij het totaal komt van de vorige 9 kolommen: Sum Abs dev: [Abs dev1]+[Abs dev2]+[Abs dev3]+[Abs dev4]+[Abs dev5]+[Abs dev6]+[Abs dev7]+[Abs dev8]+[Abs dev9]

Tot nu toe werkt het dan, maar als uiteindelijke nieuwe kolom kom dan: Expr1: [Sum Abs dev]/(9-1)
Dan krijg ik in die laatste kolom een hele hoop #Error's te zien.

Heb de SQL van de query nu erbij gedaan. Snap niet dat hij deze kolom niet wil delen want er zit toch niks raars aan?

 

[OctaFish]

Eerst maar even uitleggen hoe die functie werkt, want die is eigenlijk heel erg simpel.
Om te beginnen: de code in het codeblok moet je kopieëren en plakken in een nieuwe module, als je nog geen modules hebt tenminste. Vervolgens sla je de module op. Hoe die heet, is niet boeiend verder.
In een query maak je dan de volgende formule, al dan niet (je bent lui of niet....) via de functie Opbouwen:

Expr1: RStDev([Gas];[Electra];[Water])

Dus daar hoef je helemaal geen VBA verder voor te kennen/gebruiken! In het voorbeeld gebruik ik de velden Gas, Electra en Water. Uiteraard heb jij andere velden. Het aantal maakt niet uit, de functie kan er een hoop hebben...

 

[Deef1290]

Hoi Octafish, Ik heb de module erin geplakt en je formule gebruikt maar ik blijf een foutmelding krijgen. (The expression you entered contains invalid syntax)

Ik heb de database als voorbeeld nu erbij gedaan. Wat is er dan nu fout hier aan?

Expr1: RStDev([2009-08];[2009-09];[2009-10];[2009-11];[2009-12];[2010-01];[2010-02];[2010-03];[2010-04])

 

[OctaFish]

Hmm; moeilijk te zeggen, want ik heb precies hetzelfde gedaan in de bijgevoegde db, en daar doet-ie het perfect...

Probeer anders eens deze code in een query te plakken:

SELECT [8 Combine R + U Analysis].Plant, [8 Combine R + U Analysis].SLOC, [8 Combine R + U Analysis].[2009-08], [8 Combine R + U Analysis].[2009-09], [8 Combine R + U Analysis].[2009-10], [8 Combine R + U Analysis].[2009-11], [8 Combine R + U Analysis].[2009-12], [8 Combine R + U Analysis].[2010-01], [8 Combine R + U Analysis].[2010-02], [8 Combine R + U Analysis].[2010-03], [8 Combine R + U Analysis].[2010-04], RStDev([2009-08],[2009-09],[2009-10],[2009-11],[2009-12],[2010-01],[2010-02],[2010-03],[2010-04]) AS Expr1
FROM [8 Combine R + U Analysis];

 

[Deef1290]

Heee, om de een of andere reden werkt het nu wel. Bedankt!!