# how to calculate pooled standard deviation in MS Access

I have a question on how to calculate pooled standard deviation in MS Access. I know MS Access can use StDev(table.field) to get standard deviation. but how can I get pooled standard deviation such as subgroup size is 5? Take the following data as example, in table, there is a field "Measure" as following, set subgroup size as 5
Measure      subgroup
149.5308      1
149.5332      1
149.5288      1
149.5296      1
149.529              1
149.5323      2
149.5301      2
149.5317      2
149.5295      2
149.5328      2
149.5324      3
149.536              3
149.5306      3
149.541              3
149.5416      3
with measurement continue, the data will be continuously recorded. How to get pooled standard deviation so that I can get Cpk? Thanks.
###### 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:
I used this source: What is the pooled standard deviation?

Create a query, MeasuresAverage:
``````SELECT
Measures.Subgroup,
Count(*) AS MeasureCount,
Avg(Measures.Measure) AS GroupAverage
FROM
Measures
GROUP BY
Measures.Subgroup;
``````
and then this query, MeasuresCount:
``````SELECT
Sum(MeasuresAverage.MeasureCount) AS TotalMeasures,
Count(*) AS GroupCount
FROM
MeasuresAverage;
``````
and then the final query to collect these, MeasuresPooledStandardDeviation:
``````SELECT
Sqr(Sum(([Measure]-[GroupAverage])^2)/First([TotalMeasures]-[GroupCount])) AS PooledStandardDeviation
FROM
MeasuresCount,
Measures
INNER JOIN
MeasuresAverage
ON Measures.Subgroup = MeasuresAverage.Subgroup;
``````
The result is: 0.00315106

An Access 2016 file is attached.
PooledStandardDeviation.accdb

Experts Exchange Solution brought to you by