Average multiple fields

[jboland]

Hello All,

I am a novice, so please excuse this simple question. In a report I have several fields that average columns of information. I placed the fields in the footer of the detail, and used the average function to come up with the results for each column. What I want to do now is average all of the calculated fields together, but have it ignore the value -1. Can anybody help me?

 

[jboland]

Ok, I figured out that you can not perform a calculation on a calculated field in a report. So, i guess I need to perform the first calculation with the query. So i guess my question is still mostly the same. I need to know what criteria to enter inthe query to average together multiple fields, excluding any field with -1. Any help is greatly appreciated.

Thanks,
Brian

 

[Pat Hartman]

Select Avg(YourField)
From YourTable
Where YourField <> -1;

 

[jboland]

Ok, how do I handle the multiple fields. Say i have a field named X, Y and Z. How do I word that so it averages the fields together?

 

[Pat Hartman]

Use Excel. Your table is not normalized. Relational databases do not provide any aggregate functions that work across columns. That is because the only reason that they would be needed would be to process a structure that violates first normal form.

Solutions in order of preference:
1. Normalize your table structure.
2. Write VBA functions to perform the calculations that you need.
3. Use Excel. It provides the functions that you need.