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
CompTech810Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Gustav BrockCIOCommented:
Why not search here at EE - you would find:

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

/gustav
Rey Obrero (Capricorn1)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
tbsgadiCommented:
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Hamed NasrRetired 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;

Open in new window


Result:
sd
0.044007367
CompTech810Author 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?
Rey Obrero (Capricorn1)Commented:
the field name that you must replace is "values"  >  rs!Values
Gustav BrockCIOCommented:
I don't get it. All you need is:

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

/gustav
CompTech810Author 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.....
Hamed NasrRetired 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;

Open in new window

Rey Obrero (Capricorn1)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.
CompTech810Author 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.
Rey Obrero (Capricorn1)Commented:
ok. if you can iterate thru the fields and get its values, it will still work.
CompTech810Author 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.....
CompTech810Author Commented:
Oh, and a low spelling grade also.   'but' should be 'by'
Rey Obrero (Capricorn1)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
Gustav BrockCIOCommented:
> :- 0, now everything will change.

For sure.

/gustav
CompTech810Author 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!!
CompTech810Author 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!!
Hamed NasrRetired 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
CompTech810Author Commented:
hnasr: File uploaded.  Thanks!
Deviation.accdb
Rey Obrero (Capricorn1)Commented:
@CompTech810

your question states, "Access 2007 VBA Standard Deviation"
CompTech810Author 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
Hamed NasrRetired 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;

Open 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;

Open 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;

Open 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;

Open 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;

Open in new window

Deviation-2.accdb
CompTech810Author 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...
Rey Obrero (Capricorn1)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.
Gustav BrockCIOCommented:
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
CompTech810Author Commented:
@Rey

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

File Attached
Deviation3.accdb
Rey Obrero (Capricorn1)Commented:
see the VBA codes in Module1

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
CompTech810Author Commented:
Hi, I'm working on the samples provided.  I will let you know, thanks!
CompTech810Author 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!!!!
Hamed NasrRetired 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.