average excluding bad values

[=?iso-8859-1?B?TWFyY2Vs+A==?=]

Hi,

I have the following data:

-9999 45.56
-9999 54.51
-9999 65.82
6.22 151.44
-9999 129.67
5.96 214.84
-9999 108.3
-9999 124.31
6.1 337.32
5.72 228.56

How can I make an average in both columns excluding the bad values
(-9999) ? In the first column I want to average just 5 lines while in
the second column all the values are good. Is it possible to do this in
Access?

Thank you,

Marcelo

 

[Allen Browne]

Convert the bad values into Nulls, and you can then average the column.

In query design, type something like this into a fresh column in the Field
row:
IIf([Field1]=-9999, Null, [Field1])
substituting your field name for Field1.

Depress the Total button on the Toolbar.
Access adds a Total row to the grid.
Choose Avg under your field to get the average.

 

[=?iso-8859-1?B?TWFyY2Vs+A==?=]

Hi, thank you for your reply.

Is there a way to do this for the whole table (several columns)?

I tried a more simple solution: open the table and find/replace all the
-9999 by a empty field. However, it is not possible to turn back, to
replace an empty field by a -9999. How can I replace a -9999 by a Null,
besides this query solution?

Thanks,

Marcelo

 

[Allen Browne]

The best solution will be to use an Update query to actually replace the bad
data will Null. You will then be able to do whatever you need with your
columns.

To create an Update query, create a new query that uses this table, and
change it to an Update query (Update on Query menu.) Under the first field,
enter:
Null
in the Update row, and
-9999
in the Criteria row.
Execute the query, and repeat for your other fields.

You can reverse the process (if you can think of a good reason for doing
so), by swapping the Null into the Criteria row, and the -9999 into the
Update row. This should work, unless the field's Required property is set to
Yes, in which case Access will not permit nulls in the field.