Standard Deviation Query

[Dickx]

How would I create an Access query to calculate standard deviation of the array of Fields 1 + 2 + 3 based on the Category Field (See example below) The answer is Toys = 182.2 and Cloths = 205.8

CategoryField1 Field2 Field3
Toys 1020 1440 1620
Toys 1300 1530 1435
Toys 1250 1320 1530
Cloths 1300 1120 875
Cloths 1450 1235 1153
Cloths 1050 900 1420

 

[Nancy2]

I would start with trying to create a union query to combine the 3 fields as one, then use the Union Query as the source in a grouping query with the StDev function.

 

[Sawatzky]

Dear dickx,


select stdev(field) from
(SELECT stddev.id, stddev.name, stddev.field1 as field
FROM stddev
union
SELECT stddev.id,stddev.name, stddev.field2 as field
FROM stddev
union
SELECT stddev.id,stddev.name, stddev.field3 as field
FROM stddev ) as c
group by c.name

you need to have a primary key and you need to select it as otherwise union kicks out records with double values.

HTH
regards Astrid

 

[MichaelRed]

Union ALL does NOT kick out duplicates.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over