Access formulas

[Scott B]

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.

 

[Marshall Barton]

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.

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)

 

[Scott 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

 

[Marshall Barton]

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]

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????

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)