Get average using already calculated fields

  • Thread starter Thread starter neckface
  • Start date Start date
N

neckface

I've got a sum of the cost of an item in one column, with a grand total at
the bottom of the report.The calculation in the Total field is "=SUM([Cost])"
I want to use ths grand total to print an average, using the number of Names
displayed on the report.
ie If I had 4 names displayed and the grand total was 1000, I would want it
to take the number of displayed items from the Name field (4) and divide that
into the Grand total.
Currently I can't use a field with a control source that is a sum to input
into another sum and I was wondering if there was any way to do this. I
originally thought the calculation would go something like this:
"=SUM([Cost]/[NameField])"
However, this does not count the number of fields in NameField, so it would
not work. I looked into using the Average function in the Totals menu, but it
was greyed out when I tried to average the Total field.

Any help would be appreciated.
 
I am confused by your use of field when I think you mean control/text box. It
is important to understand the difference and accurately describe your report.

You can't sum a text box. You can usually sum the control source of a
control. You can create running sums of controls.
 
Thanks for the reply.
I did mean Text box, not field, sorry for the confusion.
I am trying to sum a text box, of which the control source is a sum. The
control source is "=Sum([Cost])" and I want to use that inside another
calculation to find an average. I think you addressed my problem when you
said "You can't sum a text box."

Thanks for trying to understand my uneducated ramblings.



Duane Hookom said:
I am confused by your use of field when I think you mean control/text box. It
is important to understand the difference and accurately describe your report.

You can't sum a text box. You can usually sum the control source of a
control. You can create running sums of controls.

--
Duane Hookom
Microsoft Access MVP


neckface said:
I've got a sum of the cost of an item in one column, with a grand total at
the bottom of the report.The calculation in the Total field is "=SUM([Cost])"
I want to use ths grand total to print an average, using the number of Names
displayed on the report.
ie If I had 4 names displayed and the grand total was 1000, I would want it
to take the number of displayed items from the Name field (4) and divide that
into the Grand total.
Currently I can't use a field with a control source that is a sum to input
into another sum and I was wondering if there was any way to do this. I
originally thought the calculation would go something like this:
"=SUM([Cost]/[NameField])"
However, this does not count the number of fields in NameField, so it would
not work. I looked into using the Average function in the Totals menu, but it
was greyed out when I tried to average the Total field.

Any help would be appreciated.
Click to expand...
Click to expand...
 
You should be able to use =Sum([cost]) in a group or report header or footer
section with expected results. You should also be able to use =Avg([Cost]) in
the same sections. Another option for average is:
=Sum([Cost])/Count(*)
--
Duane Hookom
Microsoft Access MVP


neckface said:
Thanks for the reply.
I did mean Text box, not field, sorry for the confusion.
I am trying to sum a text box, of which the control source is a sum. The
control source is "=Sum([Cost])" and I want to use that inside another
calculation to find an average. I think you addressed my problem when you
said "You can't sum a text box."

Thanks for trying to understand my uneducated ramblings.



Duane Hookom said:
I am confused by your use of field when I think you mean control/text box. It
is important to understand the difference and accurately describe your report.

You can't sum a text box. You can usually sum the control source of a
control. You can create running sums of controls.

--
Duane Hookom
Microsoft Access MVP


neckface said:
I've got a sum of the cost of an item in one column, with a grand total at
the bottom of the report.The calculation in the Total field is "=SUM([Cost])"
I want to use ths grand total to print an average, using the number of Names
displayed on the report.
ie If I had 4 names displayed and the grand total was 1000, I would want it
to take the number of displayed items from the Name field (4) and divide that
into the Grand total.
Currently I can't use a field with a control source that is a sum to input
into another sum and I was wondering if there was any way to do this. I
originally thought the calculation would go something like this:
"=SUM([Cost]/[NameField])"
However, this does not count the number of fields in NameField, so it would
not work. I looked into using the Average function in the Totals menu, but it
was greyed out when I tried to average the Total field.

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top