Making statistics on queries with varying number of columns. 1

[ViAn]

This is what I have (a query with varying number of columns AND rows):

     ColID_0 ColID_1 ColID_2 Col_ID3      ColID_end
     --      --      --       --           --
     --      --      --       --           --  
     --      --      --       --           --
       lots of rows...  
     --      --      --       --           -- 
     --      --      --       --           --

____________________________________

This is what I also want (statistics based on the query above and presented as a datasheet in a form):

Avg  --      --      --       --           --
Max  --      --      --       --           -- 
Var  --      --      --       --           --

Avg = average, Var = variance


It would be great if anyone knows a solution to this problem!


- ViAn -

 

[vbajock]

That's pretty sketchy

 

[ViAn]

My English is not very good, but thank you if it was a compliment
I think that this issue is one of the issues that should have been easy to deal with in Access and VBA. Is it?

-ViAn-

 

[vbajock]

In simpler English, I meant their is not enough information in your email for me to help you. Access is rich in ways to compute statistics.

 

[ViAn]

Ok. What I want is presented in the sketch above. But sketches often need some words to make sense:

I have a direct query whose SQL is set at run-time, based on the user's input. This query returns a lot of (but a varying number of) columns (like shown above). Each row represents a single measurement. Each column represents one parameter of the measurement. Based on each column I want to find the avg, var, max and min of all measurements for each parameter (columns). These statistic values should be presented in a datasheet that corresponds to the detailed-datasheet (like the lower section in the sketch).

To conclude:
The final result should be a form with two corresponding datasheets: The first one with rows presenting each single measurement and second one with the statistic values of each parameter (column) in the first datasheet.

Remember:
The number of columns will vary at run time!

If anybody knows and posts the solution on this problem, I would be delighted!

YS
-ViAn-

 

[MichaelRed]

A way would be to do each 'Stat' in a seperate query, such as Max: for each column in the respective aggregate queries (qryMax, qryMin, qryAvg, qryVar, ...). then do a union of these, placing the "stat" name in a calculated column. Of course, with the variable number of cols, you would need to re-generate the 'stats" queries programatically for each instance of the summary. That could be somewhat tedious if not approached carefully, but the actual SQL is quite simplistic, and with minor variation depends ONLY on the number of columns of data to to process and the actual Stat to determine. The Union Query would easily "pack" the seperate stats into a single 'recordset' for your display.







MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ViAn]

Michael,

thanks!

-ViAn-