Avg a column of unbound text boxes

[JRT2006]

I know doing calculations in a form is frowned upon, unfortunately thats the only information I've found upon hours of searching.



In my case, i think it is necessary.

As you can see, I have a subform here that does calculations to find the value of a skill. I need the average of the column to generate so i can have a "Final Value" of the prospect, then I need to store the value somehwere so a report can be generated of each prospect and their final value.
Help please...
Attached is my database for further informationDatabase8 (2).zip

[June7]

No, not necessary to do this calc on form.

Cannot use aggregate functions on textbox, period, must reference fields of RecordSource.

=Avg([GradeAvg] * [SkillValueID])

If you do the [GradeAvg] * [SkillValueID] calc in form/report RecordSource and name the constructed field like SkillRating, then on form/report:

=Avg([SkillRating])

Should not have to save this calculated value. Calculate on report. Use report Sorting & Grouping feature with aggregate calcs in group and report footers.

[JRT2006]

Okay, i think I understand what you mean. Are you saying if I reference a record source as a calculation on a form, as your example =Avg([GradeAvg]*[SkillValueID]), to get a value I DO NOT need to save that to a table, but I can still have it appear on a report in which i can sort from highest to lowest?

[June7]

SHOULD NOT save to table. This is simple calc and can easily be replicated in report design.

You want to sort on the calculated value - not sure about that.

[JRT2006]

SHOULD NOT save to table. This is simple calc and can easily be replicated in report design.

You want to sort on the calculated value - not sure about that.

I'm trying to keep the numbers on the report to a minimum. If i have to do the calculations on the report, don't all of the skill values and grades have to be on there as well?

I'd prefer the report to be as follows:

Prospect Name1 | Position | Height | Weight | College | Round | Finale Value(this would be the =Avg([GradeAvg]*[SkillValueID])
Prospect Name2 | Position | Height | Weight | College | Round | Finale Value
Prospect Name3 | Position | Height | Weight | College | Round | Finale Value

and each prospect to be listed in a row as that.

[June7]

That can be done in report design.

Or build an aggregate (GROUP BY) Totals query and use that as the report RecordSource.

[JRT2006]

That can be done in report design.

Or build an aggregate (GROUP BY) Totals query and use that as the report RecordSource.

awesome, thanks a lot for your help