Calculating average in table

[prv]

I am in the process of developing a data table and am experimenting to see if calculated fields work correctly before actually entering real data. A form


is used to enter data and place it into the table. The form correctly places data into the table. I have a problem with one of the calculated fields in the table. There are six other numeric fields (columns) which I need to average. The data represents multiple physiological measurements on the same person. Each field (column) is important and the average of the values is central to the data. I need to calculate the average of the six fields and permanently store the average as a field in the table since the values will be used in a report and it will also be exported to a statistical program. Occasionally, one of the six measurements is not possible, in which case the field is left blank. Since it is standard protocol to take six measurements, there will never be more than 6. There may be less than six if one measurement is not possible, in which case the field is left blank. Thus the need to average when one or more fields are blank. How do I calculate the average of the six fields when one or more of them may be empty? I have tried Nz function, ifnull function, ifempty functions and iif(isempty) but I cant get the calculated field to return the correct average. Apparently some of these functions cannot be used in a calculated field in a table. For simplicity sake, lets call the six measurements M1, M2...M6.

I would greatly appreciate any help on this one.

[RuralGuy]

You are having trouble because calculations belong in queries and not tables. You have given no justification yet for having a calculated field in a table.