Use of StDev in query

[Rodney]

Hello, I am trying to find the Standard Deviation
(population) of bowling scores. I have three fields called
Game1, Game2 and Game3. Can you tell me how I might setup
a query to calculate the Standard Deviation. The Query is
named BowlingQry1. The Table I am getting the data from is
called BowlingTbl. I can do simple arithmetic ( Add one
field to another) but I am having problems getting the
Standard Deviation function to work. Can you help? Thanks!

 

[Michel Walsh]

Hi,


The aggregates (MIN, MAX, SUM, COUNT, AVG, STDEV, ... ) work
vertically, not horizontally. It seems your data structure is not in the
right way (not "normalized") for their use. Consider, indeed, it is easier
to grow up vertically, adding a new record, than adding a new field (then,
the queries have to know the name of that new field).

Save the following under the name, let say, Qu1:

SELECT PlayerID, 1 As GameNumber, Game1 As Score FROM myTable
UNION ALL
SELECT PlayerID, 2, Game2 FROM myTable
UNION ALL
SELECT PlayerID, 3, Game2 FROM myTable




Next, use Qu1 as "table" for a new query. You can group by on PlayerID,
StDev on Score quite easily: click on the Summation button (on the
toolbar), a new line, Total, appear in the grid. Drag PlayerID in the grid,
keep the proposed GroupBy. Drag Score, from Qu1, in the grid, change the
GroupBy to StDev. That's all, that is as easy as it can get, when the data
is "vertical", in the intended way. Sure, it is harder to read, to consult,
but then, FORMS, not TABLES are for consultation, reading, input. Design
your tables to make your work easy; design your form to make data input
easy. Consider making Qu1 the real working table (a query does not support
index, that may become slow if you have ten thousand of records).



Hoping it may help,
Vanderghast, Access MVP