Using Avg only working for some fields

[Shimble]

I have a report that filters scores by supervisor on a form and opens a report. The report uses Avg on the fields for different categories. It seems to be working for 2 of the categories but all the rest return 0

I have the AVG fields on a group on section of the report. I can't figure out why it works for 2 fields and not the others.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[Shimble]

Attached is the file of the stripped down database. It'll give an error on load, just ignore that. open the navigation form, put in the date range of the 13th and select June for the supervisor then run the supervisor report.

[June7]

None of the Avg calcs are working for me. I find it interesting that these are all Calculated fields in table. I have never used Calculated fields. So I removed Avg function and bind to field and still get 0. I tested calculating Avg in query using Totals button from ribbon - only 2 fields work, and in table 3 fields work.

So, I did a test to perform calcs in query instead of Calculated field in table. Bind textbox to that query field. The Avg works.

Most experienced developers advise against using Calculated field in table. This is the first time I have observed justification for that.


Don't bother with sorting in query. Report design using Sorting & Grouping will override anyway.


Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

[Shimble]

That fixed it right up, thank you for the help.