# Access 2007 VBA Standard Deviation

Hello Experts!

I have created a calculation button in Access VBA that I need it to calculate the standard deviation of five fields.  I can not figure out what coding i should use.   In Excel I can get it to work.  Here are the fields values.
21.95666667
21.95166667
21.86083333
21.85138889
21.90138889

When I use the Excel's STDEVP function the result I get is 0.044007365   This is correct, but I can not figure out how to program it in VBA.

Any help is appreciated!!
LVL 2
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CIOCommented:
Why not search here at EE - you would find:

http://www.experts-exchange.com/Database/MS_Access/Q_21871694.html

/gustav
Commented:
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
Commented:
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
Retired IT ProfessionalCommented:
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;
``````

Result:
sd
0.044007367
Author Commented:
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?
Commented:
the field name that you must replace is "values"  >  rs!Values
CIOCommented:
I don't get it. All you need is:

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

/gustav
Author Commented:
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.....
Retired IT ProfessionalCommented:
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;
``````
Commented:
<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.
Author Commented:
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.
Commented:
ok. if you can iterate thru the fields and get its values, it will still work.
Author Commented:
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.....
Author Commented:
Oh, and a low spelling grade also.   'but' should be 'by'
Commented:
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
CIOCommented:
> :- 0, now everything will change.

For sure.

/gustav
Author Commented:
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!!
Author Commented:
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!!
Retired IT ProfessionalCommented:
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
Author Commented:
Deviation.accdb
Commented:
@CompTech810

your question states, "Access 2007 VBA Standard Deviation"
Author Commented:
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
Retired IT ProfessionalCommented:
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;
``````
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;
``````
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;
``````
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;
``````

Query: Q_sd_from_Table1_And_q
``````SELECT Round(Sqr(Sum(d)/(select count(n) from table1)),9) AS sd
FROM q;
``````
Deviation-2.accdb
Author Commented:
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...
Commented:
@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.
CIOCommented:
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
Author Commented:
@Rey

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

File Attached
Deviation3.accdb
Commented:
see the VBA codes in Module1

run query1
Deviation3.accdb
Author Commented:
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!!
Commented:
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?
Author Commented:
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.
Commented:
if you will be passing 5 fields at a time, the codes need to be revised to accept parameters array.
Author Commented:
Ok, typically I use the For I = 1 to 5 Next loop to increment the field using:  Me("FIELD" & I).
Commented:
@CompTech810

were you successful doing that?
Retired IT ProfessionalCommented:
Try this:
Run Query1
Code in function sd in Module1
Deviation-3.accdb

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Hi, I'm working on the samples provided.  I will let you know, thanks!
Author Commented:
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!!!!
Retired IT ProfessionalCommented:
Welcome!
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.