Stdev Aggregate function problem?

  • Thread starter Thread starter John F. Collins
  • Start date Start date
J

John F. Collins

I have a table with about 220 fields.

I have query that groups on the first five fields and gives Count on each of
the other 215 fields. It works fine

A query that groups on the first five fields and gives Avg on each of the
other 215 fields also works fine.

A query that groups on the first five fields and gives StDev on each of the
other 215 fields gives the error message: Too many fields defined.

What gives? Is this a known problem? What are the limitations of StDev?
 
John

It is a truly rare situation when a relational database table needs more
than, say, 30 fields. I suspect you've "committed spreadsheet" on Access.
While a large number of fields/columns is the only way a spreadsheet can
handle something like this, you will find you have severe headaches trying
to use aggregate functions, which operate "down", not across.

If you describe the kinds of data that you've spread across 200+ fields, the
'group readers may be able to offer another approach.

Good luck!

Jeff Boyce
<Access MVP>
 
Correction.

The number of fields in the problem were described incorrectly. When I
played around with a dummy file, I found that I could Count over 200 fields,
I could Avg about 125 fields, and could StDev about 80 fields.

Avg of 220 fields gives "query too complex". Avg of 150 fields gives "too
many fields defined".

So I suspect that Avg requires two internal fields, Sum and Count, and that
StDev requires three internal fields, Sum, SumSq, and Count. These internal
fields appear to count against the limit of 255.

John
 
Yes it is a spreadsheet type data set for which my co-worker wants counts,
means, mins, maxes, standard deviations for various groupings. The problem
is not uncommon. The data is currently stored in text or in excel files.
The data are accessed in Access by linking to the source data files. Access
is used to generate statistics.

The data represent test results from a pollutant emissions test. A few
record id columns are followed by hundreds of measured values such as
species concenetrations or mass emission rates. Here is an example of the
type of data record I am talking about:

test_date
test_start_time
client_ID
eqipment_ID
sample_port_ID
methane_ppm
ethane_ppm
propane_ppm
butane_ppm
pentane_ppm
.... etc (to hundreds of species)

There are thousands of such records, and each record has hundreds of fields.

It is possible to normalize the data as follows:

test_date
test_start_time
client_ID
eqipment_ID
sample_port_ID
species_name
concentration_ppm

That would mean 90% of the data file is useless replication of ID data. Two
tables linked on Rec_ID would be better:

RecId
test_date
test_start_time
client_ID
eqipment_ID
sample_port_ID

RecId
species_name
concentration_ppm

That way only RecID is replicated hundreds of times.

However, the text files are not stored that way. The normalized approach is
an artificial contruct handy for databases, but stupid for spreadsheets and
human review. The various species and related data within a record are
only useful in conjuntion with their counterparts within the same record.
I.e. the only thing the normalized data would be used for would be to
construct crosstabs on species name.

I've run up against this type of problem before, and built macros to
normalize or "de-crosstab" the data so that I could manipulate it more
easily. However, my co-worker has files with hundreds of columns (Excel
goes out to 256). All he wants is an easy way to get stats within various
groupings and while testing various criteria. If Access did what it said
and allowed 256 fields in a query ouput, he'd be home free.

John
 
John

What you want Access to do and what it does are not necessarily the same.
If the aggregate functions offered by Access are insufficient, since they
were design to work with normalized data, why not use the statistical
functions built into Excel?

Jeff Boyce
<Access MVP>
 
Back
Top