Hi,

I have been searching these forums for a while trying to find a
specific answer to a problem I am having. I have seen several responses
that skirt around the issue of creating form and querie averages, but
none that cover the angle I'm trying to find. I hope that it is fairly
straight-forward, as long as I write coherently! So please bear with
me.

I have a database that deals with a list of projects, each project
consisting of many tasks. I therefore have two tables, one for
projects, one for tasks, forming a one-to-many relationship. I have a
form where I can view a project; in this also a subform (in datasheet
view) where I can see the tasks that are associated with that project
(linked by a project ID).

One of the fields for the tasks is to keep track of how complete the
task is, expressed as a percentage. So when a task is in progress, I
can log on to the database, update the task [Compl_Status] field, and
so when I view the 'Project Details' form I can preview the progress of
each task in that Project.

The crux of my problem is that I want to be able to view the progress
of the *overall project*, i.e.

For ProjectTask 1 to n
Sum [Compl_Status] fields
Divide by number of Tasks associated to the Project
Display Average [Compl_Status] <==> Overall status of project

I had considered creating a field in the Projects table, to look up all
the different [Compl_Status]'s associated to that Project, and view the
average. But I understand that this would be a very bad practice, and
would result in 'bad data' - as every time I added a new task the
Project Status field wouldn't be updated. So I've resorted to try and
involve the total in either the subform, or by creating a query that
works out the average.

However, this has been to no avail. I can create a column in the
form/query which calculates an average of the column of field
[Compl_Status], but only for an individual task, not ALL the tasks.
What I envisage I need is to have the Task names forming the rows, with
columns for Task Duration, Days Completed, and of course the percentage
[Compl_Status], but with an extra 'row' at the bottom formulating the
average of the [Compl_Status] column. But I simply can't get my head
around what I'm doing wrong or how I need to edit the form I have to
accomodate what I'm trying to do.

I hope that my problem makes sense. Thanks so much for taking the time
to 'listen'! I hope that you'll be able to assist me in making sense of
what I'm trying to do!

Chris

Mothie,

In the form footer of your subform, place a textbox with the following
properties:

ControlSource: =Avg([YourField])
Visible: No

This will calculate the number you're looking for. To display it on the
main form, place a textbox that references it:

ControlSource: =[YourSubform].Form![YourSummaryField]

Sprinks

"(E-Mail Removed)" wrote:

> Hi,
>
> I have been searching these forums for a while trying to find a
> specific answer to a problem I am having. I have seen several responses
> that skirt around the issue of creating form and querie averages, but
> none that cover the angle I'm trying to find. I hope that it is fairly
> straight-forward, as long as I write coherently! So please bear with
> me.
>
> I have a database that deals with a list of projects, each project
> consisting of many tasks. I therefore have two tables, one for
> projects, one for tasks, forming a one-to-many relationship. I have a
> form where I can view a project; in this also a subform (in datasheet
> view) where I can see the tasks that are associated with that project
> (linked by a project ID).
>
> One of the fields for the tasks is to keep track of how complete the
> task is, expressed as a percentage. So when a task is in progress, I
> can log on to the database, update the task [Compl_Status] field, and
> so when I view the 'Project Details' form I can preview the progress of
> each task in that Project.
>
> The crux of my problem is that I want to be able to view the progress
> of the *overall project*, i.e.
>
> For ProjectTask 1 to n
> Sum [Compl_Status] fields
> Divide by number of Tasks associated to the Project
> Display Average [Compl_Status] <==> Overall status of project
>
> I had considered creating a field in the Projects table, to look up all
> the different [Compl_Status]'s associated to that Project, and view the
> average. But I understand that this would be a very bad practice, and
> would result in 'bad data' - as every time I added a new task the
> Project Status field wouldn't be updated. So I've resorted to try and
> involve the total in either the subform, or by creating a query that
> works out the average.
>
> However, this has been to no avail. I can create a column in the
> form/query which calculates an average of the column of field
> [Compl_Status], but only for an individual task, not ALL the tasks.
> What I envisage I need is to have the Task names forming the rows, with
> columns for Task Duration, Days Completed, and of course the percentage
> [Compl_Status], but with an extra 'row' at the bottom formulating the
> average of the [Compl_Status] column. But I simply can't get my head
> around what I'm doing wrong or how I need to edit the form I have to
> accomodate what I'm trying to do.
>
> I hope that my problem makes sense. Thanks so much for taking the time
> to 'listen'! I hope that you'll be able to assist me in making sense of
> what I'm trying to do!
>
> Chris
>
>

Sprinks,

Thank you very much, that worked, albeit with a bit of tweaking!

For some reason, after doing what you suggested it returned a value of
0. I think it was because I had a calculated Control Source for the
field - i.e. the field that was being 'averaged' was dependent on two
other fields. These other fields were not null, so resulted in a value
being returned for the [Compl_Status] field - and yet the average
didn't return properly. Can you suggest why this was so?

Many thanks

Chris


Sprinks wrote:

> Mothie,
>
> In the form footer of your subform, place a textbox with the following
> properties:
>
> ControlSource: =Avg([YourField])
> Visible: No
>
> This will calculate the number you're looking for. To display it on the
> main form, place a textbox that references it:
>
> ControlSource: =[YourSubform].Form![YourSummaryField]
>
> Sprinks

Mothie,

I'm not sure what your situation is, however, when you want to average a
calculated field, you need to average the calculation rather than the
calculated field itself. For example, if you wanted the average area of
rectangles, you need to use

Avg([Length] * [Width])

And I shouldn't have assumed that none of these might be Null, so you should
check each first, and convert any nulls to zeros:

Avg(Nz([Length]) * Nz([Width]))

This will still, however, give you the average over the total number of
records, regardless if a value is zero.

If you wish to total the average of all *non-zero* values, you could add an
invisible textbox to the Detail section with the Control Source set to:

=IIf(Nz([YourField])=0,0,1)

Let's call it, e.g., "NonZeros". You could return the non-zero average by
the following controls in the footer:

Total: =Sum([YourField])
Count: =Sum([NonZeros])
YourAverage: = [Total]/[Count]

Also, since everyone here always designs well-normalized databases , I'm
sure you really meant that your *form control* rather than your *field* was
dependent on two other fields.

Hope that helps.
Sprinks


"(E-Mail Removed)" wrote:

> Sprinks,
>
> Thank you very much, that worked, albeit with a bit of tweaking!
>
> For some reason, after doing what you suggested it returned a value of
> 0. I think it was because I had a calculated Control Source for the
> field - i.e. the field that was being 'averaged' was dependent on two
> other fields. These other fields were not null, so resulted in a value
> being returned for the [Compl_Status] field - and yet the average
> didn't return properly. Can you suggest why this was so?
>
> Many thanks
>
> Chris
>
>
> Sprinks wrote:
>
> > Mothie,
> >
> > In the form footer of your subform, place a textbox with the following
> > properties:
> >
> > ControlSource: =Avg([YourField])
> > Visible: No
> >
> > This will calculate the number you're looking for. To display it on the
> > main form, place a textbox that references it:
> >
> > ControlSource: =[YourSubform].Form![YourSummaryField]
> >
> > Sprinks
>
>

Excellent, thanks a lot - that was just what I was missing. I didn't
realise you couldn't average a field if it's *control* was dependent
on the results of other fields...

Thanks for clearing up about use of the Nz() property as well - I had
wondered why people were using it! I'll keep that in mind, although in
my case the field is dependent on required fields so should always
return a not-null value.

Chris

Sprinks wrote:

> Mothie,
>
> I'm not sure what your situation is, however, when you want to average a
> calculated field, you need to average the calculation rather than the
> calculated field itself. For example, if you wanted the average area of
> rectangles, you need to use
>
> Avg([Length] * [Width])
>
> And I shouldn't have assumed that none of these might be Null, so you should
> check each first, and convert any nulls to zeros:
>
> Avg(Nz([Length]) * Nz([Width]))
>
> This will still, however, give you the average over the total number of
> records, regardless if a value is zero.
>
> If you wish to total the average of all *non-zero* values, you could add an
> invisible textbox to the Detail section with the Control Source set to:
>
> =IIf(Nz([YourField])=0,0,1)
>
> Let's call it, e.g., "NonZeros". You could return the non-zero average by
> the following controls in the footer:
>
> Total: =Sum([YourField])
> Count: =Sum([NonZeros])
> YourAverage: = [Total]/[Count]
>
> Also, since everyone here always designs well-normalized databases , I'm
> sure you really meant that your *form control* rather than your *field* was
> dependent on two other fields.
>
> Hope that helps.
> Sprinks
>
>
> "(E-Mail Removed)" wrote:
>
> > Sprinks,
> >
> > Thank you very much, that worked, albeit with a bit of tweaking!
> >
> > For some reason, after doing what you suggested it returned a value of
> > 0. I think it was because I had a calculated Control Source for the
> > field - i.e. the field that was being 'averaged' was dependent on two
> > other fields. These other fields were not null, so resulted in a value
> > being returned for the [Compl_Status] field - and yet the average
> > didn't return properly. Can you suggest why this was so?
> >
> > Many thanks
> >
> > Chris
> >
> >
> > Sprinks wrote:
> >
> > > Mothie,
> > >
> > > In the form footer of your subform, place a textbox with the following
> > > properties:
> > >
> > > ControlSource: =Avg([YourField])
> > > Visible: No
> > >
> > > This will calculate the number you're looking for. To display it on the
> > > main form, place a textbox that references it:
> > >
> > > ControlSource: =[YourSubform].Form![YourSummaryField]
> > >
> > > Sprinks
> >
> >