StDev Question

[Bob Quintal]

I am looking to compute standard deviation and am a little
confused. If I have a table with stores and monthly sales figures
how do I run a Stdev (syntax) for the sales amounts:
Table1
Store, month1, month2, month3,month4
storeA,1000,2000,3000,4000
storeB,2000,2500,3000,3500

Any examples would be great!
Thanks! AJ

You will have to redesign the table, what you have there is a
spreadsheet.

What you need is
StoreID,Period,Amount
=====================
StoreA,month1,1000
StoreA,month2,2000
StoreA,month3,3000
StoreA,month4,4000
storeB,Month1,2000
storeB,Month2,2500
storeB,Month3,3000
storeB,Month4,3500
======================
Then you open the query design tool, add the table, bring down the
StoreID and Amount columns then click the Summation symbol in the
toolbar.
-A new row labeled Totals with the identifier Totals:
leave the words Group By under the StoreID, and select StDev under
Amount. You could also set a filter by bringing down the Period
Column and set its Totals to Where, with the filter condition in the
criteria column.

If instead of the Standard Deviation by store over several months,
you want the variance across all the stores for each month, exchange
the StoreID and Period columns.

 

[AJ]

I am looking to compute standard deviation and am a little confused. If I
have a table with stores and monthly sales figures how do I run a Stdev
(syntax) for the sales amounts:
Table1
Store, month1, month2, month3,month4
storeA,1000,2000,3000,4000
storeB,2000,2500,3000,3500

Any examples would be great!
Thanks! AJ

 

[AJ]

Redesigning the table is not an option unless I just select the values and
insert them into a new table. Can this be done a different way using VBA and
a recordset?

 

[Tom van Stiphout]

Why not? It's never too late to do the right thing. You may have to
make the changes non-billable, given that it should have been that way
to begin with.

-Tom.

Redesigning the table is not an option

<clip>

 

[John W. Vinson]

Redesigning the table is not an option unless I just select the values and
insert them into a new table. Can this be done a different way using VBA and
a recordset?

You can use a "Normalizing Union Query" -
SELECT Store, (1) AS TheMonth, Month1 AS Sales FROM tablename
UNION ALL
SELECT Store, (2) AS TheMonth, Month2 FROM tablename
UNION ALL
SELECT Store, (3) AS TheMonth, Month3 FROM tablename
UNION ALL
SELECT Store, (4) AS TheMonth, Month4 FROM tablename;

Save this query and base your statistics on it.

Or... export the data to Excel and use it as a spreadsheet.

John W. Vinson [MVP]

 

[Tom van Stiphout]

On Fri, 18 Jan 2008 22:29:59 -0700, John W. Vinson

I did this once on a project where indeed there were good reasons the
ugly tables could not be modified. Using queries like yours I created
a layer of queries that would present a normalized database design.
Then you build the rest of the application on top of that.
Not as fast as a native solution, but that's the penalty you have to
pay.

-Tom.

 

[Bob Quintal]

Redesigning the table is not an option unless I just select the
values and insert them into a new table. Can this be done a
different way using VBA and a recordset?

Redesigning the table is not an option, it's mandatory if you want
to use Access effectively.

You can use a series of insert queries to populate the new table,
one month at a time. You caqn even put the individual queries into
one query definition using the UNION keyword. John Vinson told you
how to do that.

Q

 

[AJ]

Redesigning is not an issue because this is an old database that is used for
dozens of other things and I am jsut trying to write a quick VBA procedure
against it. Trust me, I realize it would be much easier if it was normalized
but that is why I am having such trouble with how to do this.

 

[AJ]

Then once I do this I can somehow run a standard deviation against the query?
THis is all in a vba procedure, does that change anything? I am really new to
access so any examples would be great. Thanks in advance!

 

[Tom van Stiphout]

Yes you can. Create a new query. Select John's query. Use the StDev
function to create your standard deviation.
Then in VBA open a recordset on that query (let's call it qryStDev)
dim rs as dao.recordset
set rs=currentdb.querydefs("qryStDev")
debug.print rs(0) 'check out the value in the first column

-Tom.