Access formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that I have created a report from. The calculations are done
on the report itself. For example field c was blank on the table so I created
a formula in there a/b, that worked. Now I am trying to get the avg of c. It
won't work.
 
Scott said:
I have a table that I have created a report from. The calculations are done
on the report itself. For example field c was blank on the table so I created
a formula in there a/b, that worked. Now I am trying to get the avg of c. It
won't work.
Click to expand...


When you want to aggregate a value across multple records,
you need a way to specify the the group of records. In a
report, this is either all the records in the entire report
or the records in a group specified in Sorting and Grouping.
Then the expression/value you want to aggregate would be
used in a text box in the corresponding header or footer
section. One very important point is that the aggregate
functions only operate on fields in the report's record
source table/query, they are unaware of calculations done in
controls on the report. Another important point is that all
the aggregate functions, except Count(*), ignore Null
values.

Putting all that together, I think you need to use:
=Avg(a / b)
 
Thanks for the info Marsh,

I have been working on it, I had a if statement in there to get rid of the
"Div0" that was causing my problem,=IIf([Square Feet Structure]>0,[Sale Value
$]/[Square Feet Structure],"") however now that I can get the average. The
Div01 has come back. How do I get rid of it with out using iif????

Thanks
scott
 
Note that you should use Null instead of "" when you want
the result to represent no value.

Just use the same expression in the Sum function.

=Avg(IIf([Square Feet Structure]>0, [Sale Value $]/[Square
Feet Structure], Null))
--
Marsh
MVP [MS Access]


Scott said:
I have been working on it, I had a if statement in there to get rid of the
"Div0" that was causing my problem,=IIf([Square Feet Structure]>0,[Sale Value
$]/[Square Feet Structure],"") however now that I can get the average. The
Div01 has come back. How do I get rid of it with out using iif????


Marshall Barton said:
When you want to aggregate a value across multple records,
you need a way to specify the the group of records. In a
report, this is either all the records in the entire report
or the records in a group specified in Sorting and Grouping.
Then the expression/value you want to aggregate would be
used in a text box in the corresponding header or footer
section. One very important point is that the aggregate
functions only operate on fields in the report's record
source table/query, they are unaware of calculations done in
controls on the report. Another important point is that all
the aggregate functions, except Count(*), ignore Null
values.

Putting all that together, I think you need to use:
=Avg(a / b)
Click to expand...
Click to expand...
 
Back
Top