query data then average results

  • Thread starter Thread starter dnr
  • Start date Start date
D

dnr

I am working with data collected automatically. When the sensor collecting
the data is not working it displays really large numbers. I need to get an
average of the data.

I am trying to first eliminate the really large numbers then get an average
of the remaining data. I have to do this on 30 different columns of data. I
want to automate this - but I am struggling. I have created a form with a
combo box that list the field names on the table. I have a text box that I
want the average to be displayed in and then a command button that I want to
click after I have selected the field I want to evaluate. I know I want to
search the field and use only records less then 3000. I want to then average
that data. Please advise on how to go about writting the code.
 
On Mon, 21 Apr 2008 09:04:04 -0700, dnr

Can't you use the DAvg function? The Textbox controlsource could be
set to:
=DAvg(MyCombobox, "MyTable", MyCombobox & " < 3000")

Personally I would have an extra table with "meta-data", with fields
including FieldName, MaxReliableValue, etc. Then you could modify the
DAvg function and use the MaxReliableValue specific to each field.
This because I'm thinking a temperature sensor will have a different
MaxReliableValue than a pressure sensor.

Also, having 30 columns is a REALLY BAD IDEA and violates important
relational database design rules.
Rather you should probably have:
SensorID
DateMeasured
MeasuredValue
Among other things, this would make it trivial to add another sensor
(just add a row to tblSensors), or to find the lowest or highest value
of a set of identical sensors, etc, etc.

-Tom.
 
Tom,

Thanks for the response. I am still having trouble though, when I use the
DAvg function, I keep getting the #Name! error. Any advice? Also, you
mentioned 30 columns was bad. I get the data in spreadsheet form there are
30 sensors and one timestamp - a reading for each sensor is taken at each
time stamp. Would you still advise making 30 different tables?
 
On Tue, 22 Apr 2008 12:29:01 -0700, dnr

No, that's not what I said. I would created one table with three
fields:
SensorID
DateMeasured
MeasuredValue
and each row of XL data would be turned into 30 rows of Access data.

#Name! usually means that your control references are wrong. For
example:
=DAvg(MisspelledCombobox, "MyTable", MyCombobox & " < 3000")
would cause this problem. It is NOT an error specific to DAvg.

-Tom.
 
Back
Top