STDEV arcoss multiple fields/tables

[Scottyk537]

Below is a Tab Delimited section of text to represent a SQL Table.

This is the result set of a select * from table where RNmbr = 0508.

I added the Top Row of A TAB B…TAB H

Row 1 references Column names of SQL Table.

And the First Column (under A) as Excel references.



A B C D E F G

1 RNmbr MesPt R1 R2 R3 R4

2 0508-1 1 28.0 48.0 48.0 74.0

3 0508-1 2 77.0 78.0 75.0 48.0

4 0508-1 3 81.0 59.0 65.0 56.0

5 0508-1 4 54.0 46.0 24.0 25.0

6 0508-1 5 21.0 2.0 15.0 74.0

7 0508-1 6 4.0 88.0 68.0 14.0

8 0508-1 7 8.0 94.0 87.0 96.0

9 0508-1 8 9.0 76.0 66.0 58.0

10 0508-1 9 48.0 48.0 35.0 74.0

11 0508-1 10 36.0 59.0 26.0 888.0



The challenge:

We can easily create STDEV(R1), STDEV(R2), STDEV(R3), STDEV(R4) from the above info.

However, we need to do a STDEV of all these points combined.

Within Excel, this is easily accomplished with the formula

= STDEV(D211,E2:E11,F2:F11,G2:G11)



However, we are tasked of getting away from an Excel spreadsheet and putting this data into a database (imagine the above several hundred thousand strong).

We are using Access as a front end to SQL 2000. We need to have this conglomerate STDEV to be within Access (should the resulting STDEV fall out of spec) it will trigger an alarm for the production operator.

We are not having much success using T-SQL or Access in getting something which seems so simple in Excel. An Access query would suit our needs better.


Now, once this hurdle is overcome; there is a second phase. This table represents one of three, and, you guessed it, there is a need for a Conglomerate STDEV of ALL these points. Any help would be greatly appreciated.

 

[Scottyk537]

kindda there - but not quite

WE know the following works in T-sql.

SELECT STDEV(t.p)
FROM (SELECT R1 p FROM MyTable WHERE rollnmbr = '0508-1'
UNION ALL
SELECT R2 FROM MyTable WHERE rollnmbr = '0508-1'
UNION ALL
SELECT R3 FROM MyTable WHERE rollnmbr = '0508-1'
UNION ALL
SELECT R4 FROM MyTable WHERE rollnmbr = '0508-1'
) t

However, when we run it in Access Query Builder we get the error:
Syntax error (missing operator) in query expression 'R1 p'.
(Frankly, we don't quite understand the 'p' and 't' reference in the above command, other than it works in T-SQL). When we remove the 'p' we get the error:
Could not find file 'C:\my documents\dbo.mbd'
???

Any ideas on how to transcribe the above to work within Access?

 

[Jon K]

In Access, you can do it with two queries.

qryOne:-
SELECT R1 as Num FROM MyTable WHERE Rollnmbr="0508-1"
UNION ALL
SELECT R2 FROM MyTable WHERE Rollnmbr="0508-1"
UNION ALL
SELECT R3 FROM MyTable WHERE Rollnmbr="0508-1"
UNION ALL
SELECT R4 FROM MyTable WHERE Rollnmbr="0508-1";

qryTwo:-
SELECT StDev([Num]) as StDev
FROM qryOne;


Run the second query.

 

[Scottyk537]

It Works!!!!!!

Thanks Jon. It works great. You saved us a lot of time

 

[Scottyk537]

Also.......

How would I take this statement and link it to my form?
I would think that I need another column to be my Rollnmbr.
How is this written in Access? I cannot view the Query in Design mode to add another column.
Something like this?

SELECT Rollnmbr FROM MyTable,
SELECT R1 as Num FROM MyTable WHERE Rollnmbr="*"
UNION ALL
SELECT R2 as Num FROM MyTable WHERE Rollnmbr="*"
UNION ALL
SELECT R3 as Num FROM MyTable WHERE Rollnmbr="*"
UNION ALL
SELECT R4 as Num FROM MyTable WHERE Rollnmbr="*";

Thanks for all your help........