Creating a column-average in subform datasheet

[mothie]

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

 

[Guest]

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

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]

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

 

[Guest]

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

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

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]

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

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

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

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