Using Avg

  • Thread starter Thread starter Cathy
  • Start date Start date
C

Cathy

I am using Access 2000 and in one of my reports I have
seven categories (Closed, Expired, Proposed, etc.). I
created an expression that subtracts the completed date
for each task from the current date and the result is
captured in [OpenProp]. What I would like to do is to
calculate the average days opened for the Proposed
category only. My calculation at the end of the report
is "Avg([OpenProp])" and it takes the average for all of
the categories. I tried "Avg([OpenProp].[Proposed])"
and "Avg([OpenProp].[Status]="Proposed")" and I got an
empty response.
Can you help me please?
 
You can't do a calculation using calculated fields. I
suggest you base your report on a query and calculate
[openProp] in the query. Display the field [OpenProp] in
the report and =Avg([OpenProp]) will work.

Jim
 
Thanks Jim! What I ended up doing was changing the
expression in my query to "OpenProp: IIf([Status]
="Proposed",Date()-[Date Completed],0)" and the results
are only the Proposed files show the number of days
proposed and that is part of what I wanted. However, I
still have a problem in the report of showing an average
for the Proposed files only. The result that I get is 89
days and I know that this is understated. The box that I
created in the report footer has the following under the
Control Source "=Avg([OpenProp])" and it's taking all of
the records in the average calculation. If I have a blank
inserted in the other files instead of 0, then I get an
error message and the report won't run. Any ideas?
Thanks
-----Original Message-----
You can't do a calculation using calculated fields. I
suggest you base your report on a query and calculate
[openProp] in the query. Display the field [OpenProp] in
the report and =Avg([OpenProp]) will work.

Jim
-----Original Message-----
I am using Access 2000 and in one of my reports I have
seven categories (Closed, Expired, Proposed, etc.). I
created an expression that subtracts the completed date
for each task from the current date and the result is
captured in [OpenProp]. What I would like to do is to
calculate the average days opened for the Proposed
category only. My calculation at the end of the report
is "Avg([OpenProp])" and it takes the average for all of
the categories. I tried "Avg([OpenProp].[Proposed])"
and "Avg([OpenProp].[Status]="Proposed")" and I got an
empty response.
Can you help me please?
.
Click to expand...
.
Click to expand...
 
Back
Top