Access 2007 VBA Standard Deviation

Why not search here at EE - you would find:

https://www.experts-exchange.com/questions/21871694/Standard-Deviation-in-Access.html

/gustav

test this code, replace tablename and field name accordingly

Sub StdDEV_VBA()
Dim rs As DAO.Recordset, j As Integer, vArr() As String, recCount As Integer
Dim stdDevPx As Double, Xm As Double, xVal As Double, xVar As Double
Set rs = CurrentDb.OpenRecordset("tblDev")
rs.MoveLast
recCount = rs.RecordCount
rs.MoveFirst
Do Until rs.EOF
    ReDim Preserve vArr(j)
    vArr(j) = rs!Values
    j = j + 1
    xVal = xVal + rs!Values
    rs.MoveNext
Loop
Xm = xVal / recCount 'mean

For j = 0 To UBound(vArr)
    xVar = xVar + (vArr(j) - Xm) ^ 2
Next

stdDevPx = Sqr(xVar / recCount)
Debug.Print stdDevPx

End Sub

Something like this function explained here

Using Excel Functions in Queries

Using the standard setup you cannot use an excel function within a query. However, you can mask a function and borrow excel functionality to achieve the same result.

Create a new module or open an existing one. Paste the following code in.

Public Function ExcelCosH(Angle As Double) As Double
     ExcelCosH = Excel.WorksheetFunction.Cosh(Angle)
End Function

Add a reference to Microsoft Excel, and then modify the function line to the particular function you need. The one I've shown is the CosH function, although you can use almost any of the excel functions availiable. Also change the name of the function to something to remember (not forgetting to replace the name as the start of the second line). It may well be worth adding some checking code to make sure the value you pass is not null or out of the bounds.

Then in the query you can just use your newly created mask function.


http://www.fabalou.com/Access/Queries/excel_functions.asp

Try this as a query:

Table1(id, n)
id      n
1      21.95666667
2      21.95166667
3      21.86083333
4      21.85138889
5      21.90138889

Query:

SELECT round(SQR(sum(d)/(select count(n) from table1)),9) AS sd
FROM (SELECT Table1.id, Table1.n, (n-(select Avg([n]) from table1))^2 AS d
FROM Table1)  AS q;

Select allOpen in new window

Result:
sd
0.044007367

Thank you everyone for your quick responses.

Gustav:  I did a search but the search was very limited, it's not like a google search.

Rey:  I'm currently looking at your code and I am assuming the the file name I should replace with my file name is: tblDev  and the field name I should replace with my field name is: vArr, correct?

tbsgadi:  That is very cool using excel and access together but I want to keep the coding contained with in Access.

hnsar:  Are results are not the same that I get from Excel?

the field name that you must replace is "values"  >  rs!Values

I don't get it. All you need is:

SELECT StDevP([YourField])) AS StDevOfYourField FROM YourTable;

/gustav

Gustav:  I maybe over thinking this.  I had the same feeling this can't be that difficult.  The values are stored in different fields not in multiple records with the same field name.  Exp.  Field1 = 21.95666667 , Field2 = 21.95166667, etc.....

It is the same as given by STDEVP in excel.

That is for the population.

For a sample use: -- dividing by n-1 instead of n for population

SELECT round(SQR(sum(d)/((select count(n) from table1)-1)),9) AS sd
FROM (SELECT Table1.id, Table1.n, (n-(select Avg([n]) from table1))^2 AS d
FROM Table1)  AS q;

Select allOpen in new window

<The values are stored in different fields not in multiple records with the same field name.  Exp.  Field1 = 21.95666667 , Field2 = 21.95166667, etc..... >

:- 0, now everything will change.

Rey: Sorry about that.  Also, they are fields on a form but do get feed into a file as the fields are populated.  I use Me("FIELD" & F)  where F is a counter.

ok. if you can iterate thru the fields and get its values, it will still work.

Well, now I know why I got a low grade in Algebra II.....    Lol

I know I can spin through the data but using:

For F = 1 to 5

Next F

I'm lost on how to do a STDEV on each field to get a STDEVP , hope this makes since.....

Oh, and a low spelling grade also.   'but' should be 'by'

test this code, just change "tblDev" with name of your table

Sub StdDEV_VBA()
Dim rs As DAO.Recordset, j As Integer, vArr() As String, recCount As Integer, idx As Integer
Dim stdDevPx As Double, Xm As Double, xVal As Double, xVar As Double
Set rs = CurrentDb.OpenRecordset("tblDev1")
Do Until rs.EOF
    For idx = 0 To rs.Fields.Count - 1
        ReDim Preserve vArr(j)
        vArr(j) = rs(idx)
        j = j + 1
        xVal = xVal + rs(idx)
    Next
    rs.MoveNext
Loop

Xm = xVal / idx  'mean

For j = 0 To UBound(vArr)
    xVar = xVar + (vArr(j) - Xm) ^ 2
Next

stdDevPx = Sqr(xVar / idx)
Debug.Print stdDevPx

End Sub

> :- 0, now everything will change.

For sure.

/gustav

hnsar:  I'm still fairly new with VBA, I'm an RPG programmer.  Could you attach a sample access file that I can walk through to better understand what the statement is doing?  Thanks!!

hnasr: I created my own access table and query and got the same results you did.  I broke down the query that you provided.  Starting with 1.  Count = 5, 2. Sum(109.52194445) divided  by count(5) 3. SQR of 21.90438889 = 4.680212483.   Do you know what I'm doing wrong.  Also, what is the 'd' in sum(d)?  Thanks so much!!

Welcome!
Can you upload the database with just the table and the query?

The query is compound, which calculates from a query based on the table.
I'll try to break it up when you upload the sample database. I already done that but the database was overwritten.

d is an alias of a field in inner query:

 (n-(select Avg([n]) from table1))^2 AS d

hnasr: File uploaded.  Thanks!
Deviation.accdb

@CompTech810

your question states, "Access 2007 VBA Standard Deviation"

Yes, I did.  I'm just spinning my wheels trying to get this to work.  I'm trying to break down the query so I understand it and then I will apply it to VBA coding.  None of the answers  given do I understand.  This can't be that hard, again I must  be over looking something so obvious.  Rey, I'm completely lost when I read through your code.  For some reason there is an EOF in both responses.  I'm not reading to the the end of file but reading across the record.  Hope this make since.  Again, I'm fairly new to VBA

Queries modified

Check the database.
Table1:
ID2      ID      n
1      1      21.95666667
2      2      21.95166667
3      3      21.86083333
4      4      21.85138889
5      5      21.90138889
Query: Q_diff_from_mean_square to prepare data from table1

SELECT Table1.ID, Table1.n, (select Avg([n]) from table1) AS pop_mean, [n]-[pop_mean] AS diff_from_mean, [diff_from_mean]^2 AS diff_from_mean_square
FROM Table1;

Select allOpen in new window

ID      n      pop_mean      diff_from_mean      diff_from_mean_square
1      21.95666667      21.90438889      0.05227778      2.7329662817284E-03
2      21.95166667      21.90438889      0.04727778      2.2351884817284E-03
3      21.86083333      21.90438889      -0.04355556      1.8970868069136E-03
4      21.85138889      21.90438889      -0.053      0.002809
5      21.90138889      21.90438889      -0.003      0.000009

Query: Q_sd_from_Table1_And_Q_diff_mean_square to calculate sd from Q_diff_from_mean_square

SELECT Round(Sqr(Sum(diff_from_mean_square)/(select count(n) from table1)),9) AS sd
FROM Q_diff_from_mean_square;

Select allOpen in new window

sd
0.044007367

Query: sd_from_table1_compuound_query uses Table1 only to calculate sd, other data is prepared in a sub query.

SELECT Round(Sqr(Sum(d)/(select count(n) from table1)),9) AS sd
FROM (SELECT Table1.id, Table1.n, (n-(select Avg([n]) from table1))^2 AS d FROM Table1)  AS q;

Select allOpen in new window

sd
0.044007367

Above query can be split to q and Q_sd_from_Table1_And_q
Query: q

SELECT Table1.id, Table1.n, (n-(select Avg([n]) from table1))^2 AS d
FROM Table1;

Select allOpen in new window

Query: Q_sd_from_Table1_And_q

SELECT Round(Sqr(Sum(d)/(select count(n) from table1)),9) AS sd
FROM q;

Select allOpen in new window

Deviation-2.accdb

OK, I've got to be the biggest idiot.  I've been working on this for days and still can not get it to come out correctly.  When I use your coding  of field (n)  = 21.95666667 , NEXT RECORD field (n)  = 21.95166667, etc.....  it works.  I can not translate it to the way my table is set up witch is FIELD1 = 21.95666667, FIELD2 = 21.95166667, etc on the same record.  Ugghh...

@CompTech810

the query will not work in the current table set up that you have.

to make it work on a query, you will need the VBA codes i posted above.

upload a db with the table that you have.

You could also use a simple union Query where Measure1-5 are your fields:

SELECT
    StDevP(Measure) AS StMeasure
FROM
(SELECT  Measure1 As Measure
FROM tblSample
WHERE Measure5 Is Not Null
UNION ALL
SELECT  Measure2 As Measure
FROM tblSample
WHERE Measure5 Is Not Null
UNION ALL
SELECT  Measure3 As Measure
FROM tblSample
WHERE Measure5 Is Not Null
UNION ALL
SELECT  Measure4 As Measure
FROM tblSample
WHERE Measure5 Is Not Null
UNION ALL
SELECT  Measure5 As Measure
FROM tblSample
WHERE Measure5 Is Not Null)

Result: 4.40073665887496E-02

/gustav

@Rey

I know that, I'm just trying to use the logic and apply it to the VBA

File Attached
Deviation3.accdb

Wow, that was quick and it is producing the correct result.  

Could you please explain how the coding works and what is the MODULE1 for, I don't see it being used.  I need to apply it to the other fields.  There are multiples of fives, so the next set would be FIELD6, FIELD7, FIELD8, FIELD9, FIELD10 and so on.     THANKS!!

Module1 is the module that house the function  fncStdDEV_VBA that is used in Query1

< There are multiples of fives, so the next set would be FIELD6, FIELD7, FIELD8, FIELD9, FIELD10 >

where are these fields coming from?

Ahhh, I see it now, can you tell I don't use Access that much.  Opps, I didn't include Fields 6 - 10 in the Table.  The live table goes to over 100 fields.  The fields are on a form In columns of five fields, in each column.

if you will be passing 5 fields at a time, the codes need to be revised to accept parameters array.

Ok, typically I use the For I = 1 to 5 Next loop to increment the field using:  Me("FIELD" & I).

@CompTech810

were you successful doing that?

Hi, I'm working on the samples provided.  I will let you know, thanks!

Sorry for the delay on getting back with everyone. hnasr I was able to take your code and understand it and applied the logic that you gave with a GREAT SUCCESS!!  Rey, I also want to thank you for your code, as it helped also.

Thanks so much!!!!

Welcome!