StdDeviation sideways.

[Stephen Moncrief]

I am looking for a function to calculate stdDev of a
group of fields, rather than a group of records. The
data looks something like this:

part Jan Feb Mar April etc
123 250 100 125 100 ...
124 10 25 40 35 ...
125 100 25 50 55 ...
I have over 16000 records in this table and need to know
the std deviation of the past 12 months use, listed in
the month use fields. I have tried StDev and DstDev in
built in functions, but I don't think that will work. I
can make this work in Excel, so I should be able to do it
in Access as well. Any suggestions?

 

[Duane Hookom]

The issue is your table structure which resembles a spreadsheet rather than
a database table. If your table structure was:
Part Mth TheValue
Then you could use a query and stDev in a totals query.

You could create a union query based on your existing table:
SELECT Part, 1 as MthNum, Jan as TheValue
FROM tblA
UNION ALL
SELECT Part, 2, [Feb]
FROM tblA
UNION ALL
.... etc ...

Then create a totals query based on the union query.

 

[Jeff Boyce]

Stephen

As Duane points out, the functions in Access are predicated on a
well-normalized data structure. If you cannot modify the data structure,
one approach would be to change the tool -- export the data to Excel and use
the functions available there...