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