Row level statistics

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I have a query which displays N different fields:

Date Par1 Par2 Par3 ... ParN
.... $10 $20 $30 .... ...
.... .... .... ... .... ...

I would like to design a query that calculates basic statistics of
these fields (Avg, Var, Stdev, ..) and displays them in the following
way:

Var Avg Var StDev
Par1 ... ... ....
Par2 ... ... ....
Par3 ... ... ....
..... ... ... ....
ParN ... ... ....

Currently, a query computes these statistics, but all results are
lined up in a single row (ie. Par1Avg, Par1Var, Par1StDev, Par2Avg,
Par2Var, etc..)

Is there a clever way to design this query so that the results will be
displayed columnwise a shown above?

Thanks for any suggestion.
 
Hi,


Database tools are supplied to work vertically, not horizontally. You
have to normalize your data, maybe with something like:


SELECT "Par1" As piece, Par1 As Price FROM mytable
UNION ALL
SELECT "Par2", Par2 FROM myTable
UNION ALL
SELECT "Par3", Par3 FROM myTable
....
UNION ALL
SELECT "ParN", ParN FROM myTable


Save that query under the name, for illustration, QU.


In a new query, based on QU rather than on the original table, click on the
summation button on the toolbar, that will append a new line, Total, in the
grid. Drag the field piece in the grid, keep the proposed GroupBy. Drag
Price in the grid, change the GroupBY to AVG, drag it again, change the
GroupBy to SDev. You can also type Price^2 in a free column, and change
the GroupBy to SUM to get the SUM(Price^2), as example.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top