averaging subtotals on report

[jkatj]

I've created a report to show the amount of time that is spent on each
project my department works on. It's grouped first by client and then by
individual project. Each individual project is broken down into how much time
was spent by each employee on the project. At the end of the project is a
total of the time spent on that project, at the end of each client is a total
of the time spent on all projects for that client, and at the end of the
report is a total of all the time spent on all the projects listed on the
report. So far so good.

At the end of the report, I'd also like to show the average amount of time
for an individual project. However, all I can conjure up is the average of
the line items of all the projects, which isn't helpful at all. The formula
giving me that average is "=Avg([WorkerTime])" and obviously isn't the
formula I need in the Report Footer. So I tried "=Avg([SubtotalWorkerTime])"
where "SubtotalWorkerTime" is the name of the text box with the total time
worked for a project, but that yielded only a prompt for SubtotalWorkerTime
upon opening the report.

I've played around in queries trying to find a way to get one to calculate
what I need, but I've been completely unsuccessful.

Does anyone have any advice on how to get the average I seek in my report?
It's probably something painfully obvious, but I'm just not finding it.

Thanks in advance for any help.

 

[Jeff Boyce]

I'm not clear on what you are trying to compute the average of. But if the
numbers you seek to average are totals (or subtotals) of other numbers, or
if they are themselves already averages, you'll want to carefully consider
how you are doing the calculation.

If the totals (or averages) have the same number of elements in EVERY value,
an average makes sense. If the totals or averages you seek to average have
differing numbers of elements, taking THEIR average makes NO sense.

A recovering statistician...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jkatj]

Here is a rather simplified example:

ABC Company

Newspaper Ad
Joe's time 5hrs
Frank's time 7hrs
Judy's time 3hrs
Newspaper Ad total time 15hrs

Magazine Ad
Judy's time 12hrs
Sarah's time 4hrs
Frank's time 10hrs
Bob's time 7hrs
Marsha's time 1hr
Magazine Ad total time 34hrs


ABC Company total time 49hrs

Company 2
Newspaper Ad
Joe's time 11hrs
Judy's time 1hr
Newspaper Ad total time 12hrs

Magazine Ad
Sarah's time 5hrs
Bob's time 1hr
Marsha's time 1hr
Magazine Ad total time 7hrs

Mail Ad
Joe's time 6hrs
Mail Ad total time 6hrs

Company 2 total time 25hrs

Report's total time on ads 74hrs
Average time per project 14.8hrs

--------------------

The current worthless average showing on report is 5.3hrs (the average
amount of time any worker spent on any project: 74hrs/14entries). I don't
care about that number. What I want to know is on average how much time is
spent on a complete project (74hrs/5projects). I think the average time spent
on each client would also be useful data (74hrs/2clients). However, I can't
figure out how to get either of these averages to calculate on my report.

Does that make it clearer? Any suggestions?

I'm not clear on what you are trying to compute the average of. But if the
numbers you seek to average are totals (or subtotals) of other numbers, or
if they are themselves already averages, you'll want to carefully consider
how you are doing the calculation.

If the totals (or averages) have the same number of elements in EVERY value,
an average makes sense. If the totals or averages you seek to average have
differing numbers of elements, taking THEIR average makes NO sense.

A recovering statistician...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've created a report to show the amount of time that is spent on each
project my department works on. It's grouped first by client and then by
individual project. Each individual project is broken down into how much
time
was spent by each employee on the project. At the end of the project is a
total of the time spent on that project, at the end of each client is a
total
of the time spent on all projects for that client, and at the end of the
report is a total of all the time spent on all the projects listed on the
report. So far so good.

At the end of the report, I'd also like to show the average amount of time
for an individual project. However, all I can conjure up is the average of
the line items of all the projects, which isn't helpful at all. The
formula
giving me that average is "=Avg([WorkerTime])" and obviously isn't the
formula I need in the Report Footer. So I tried
"=Avg([SubtotalWorkerTime])"
where "SubtotalWorkerTime" is the name of the text box with the total time
worked for a project, but that yielded only a prompt for
SubtotalWorkerTime
upon opening the report.

I've played around in queries trying to find a way to get one to calculate
what I need, but I've been completely unsuccessful.

Does anyone have any advice on how to get the average I seek in my report?
It's probably something painfully obvious, but I'm just not finding it.

Thanks in advance for any help.

 

[Jeff Boyce]

So, you're (not) saying that each ad-type for each company is a "project".
How are you storing this information in your table(s)? I ask because how
you get data/information back out depends heavily on how you have it stored.

What's your table structure for this kind of data?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is a rather simplified example:

ABC Company

Newspaper Ad
Joe's time 5hrs
Frank's time 7hrs
Judy's time 3hrs
Newspaper Ad total time 15hrs

Magazine Ad
Judy's time 12hrs
Sarah's time 4hrs
Frank's time 10hrs
Bob's time 7hrs
Marsha's time 1hr
Magazine Ad total time 34hrs


ABC Company total time 49hrs

Company 2
Newspaper Ad
Joe's time 11hrs
Judy's time 1hr
Newspaper Ad total time 12hrs

Magazine Ad
Sarah's time 5hrs
Bob's time 1hr
Marsha's time 1hr
Magazine Ad total time 7hrs

Mail Ad
Joe's time 6hrs
Mail Ad total time 6hrs

Company 2 total time 25hrs

Report's total time on ads 74hrs
Average time per project 14.8hrs

--------------------

The current worthless average showing on report is 5.3hrs (the average
amount of time any worker spent on any project: 74hrs/14entries). I don't
care about that number. What I want to know is on average how much time is
spent on a complete project (74hrs/5projects). I think the average time
spent
on each client would also be useful data (74hrs/2clients). However, I
can't
figure out how to get either of these averages to calculate on my report.

Does that make it clearer? Any suggestions?

I'm not clear on what you are trying to compute the average of. But if
the
numbers you seek to average are totals (or subtotals) of other numbers,
or
if they are themselves already averages, you'll want to carefully
consider
how you are doing the calculation.

If the totals (or averages) have the same number of elements in EVERY
value,
an average makes sense. If the totals or averages you seek to average
have
differing numbers of elements, taking THEIR average makes NO sense.

A recovering statistician...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've created a report to show the amount of time that is spent on each
project my department works on. It's grouped first by client and then
by
individual project. Each individual project is broken down into how
much
time
was spent by each employee on the project. At the end of the project is
a
total of the time spent on that project, at the end of each client is a
total
of the time spent on all projects for that client, and at the end of
the
report is a total of all the time spent on all the projects listed on
the
report. So far so good.

At the end of the report, I'd also like to show the average amount of
time
for an individual project. However, all I can conjure up is the average
of
the line items of all the projects, which isn't helpful at all. The
formula
giving me that average is "=Avg([WorkerTime])" and obviously isn't the
formula I need in the Report Footer. So I tried
"=Avg([SubtotalWorkerTime])"
where "SubtotalWorkerTime" is the name of the text box with the total
time
worked for a project, but that yielded only a prompt for
SubtotalWorkerTime
upon opening the report.

I've played around in queries trying to find a way to get one to
calculate
what I need, but I've been completely unsuccessful.

Does anyone have any advice on how to get the average I seek in my
report?
It's probably something painfully obvious, but I'm just not finding it.

Thanks in advance for any help.

 

[jkatj]

To answer your first inquiry: for the sake of this discussion, yes, each ad
type for a client is a "project."

I'm afraid I don't really know how to answer your questions "How are you
storing this information in your table(s)?" and "What's your table structure
for this kind of data?" My tables have fields with the data in them. If
there's another way to store data in Access, I don't know it. I'm very much a
beginner in the program.

So, you're (not) saying that each ad-type for each company is a "project".
How are you storing this information in your table(s)? I ask because how
you get data/information back out depends heavily on how you have it stored.

What's your table structure for this kind of data?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is a rather simplified example:

ABC Company

Newspaper Ad
Joe's time 5hrs
Frank's time 7hrs
Judy's time 3hrs
Newspaper Ad total time 15hrs

Magazine Ad
Judy's time 12hrs
Sarah's time 4hrs
Frank's time 10hrs
Bob's time 7hrs
Marsha's time 1hr
Magazine Ad total time 34hrs


ABC Company total time 49hrs

Company 2
Newspaper Ad
Joe's time 11hrs
Judy's time 1hr
Newspaper Ad total time 12hrs

Magazine Ad
Sarah's time 5hrs
Bob's time 1hr
Marsha's time 1hr
Magazine Ad total time 7hrs

Mail Ad
Joe's time 6hrs
Mail Ad total time 6hrs

Company 2 total time 25hrs

Report's total time on ads 74hrs
Average time per project 14.8hrs

--------------------

The current worthless average showing on report is 5.3hrs (the average
amount of time any worker spent on any project: 74hrs/14entries). I don't
care about that number. What I want to know is on average how much time is
spent on a complete project (74hrs/5projects). I think the average time
spent
on each client would also be useful data (74hrs/2clients). However, I
can't
figure out how to get either of these averages to calculate on my report.

Does that make it clearer? Any suggestions?

I'm not clear on what you are trying to compute the average of. But if
the
numbers you seek to average are totals (or subtotals) of other numbers,
or
if they are themselves already averages, you'll want to carefully
consider
how you are doing the calculation.

If the totals (or averages) have the same number of elements in EVERY
value,
an average makes sense. If the totals or averages you seek to average
have
differing numbers of elements, taking THEIR average makes NO sense.

A recovering statistician...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've created a report to show the amount of time that is spent on each
project my department works on. It's grouped first by client and then
by
individual project. Each individual project is broken down into how
much
time
was spent by each employee on the project. At the end of the project is
a
total of the time spent on that project, at the end of each client is a
total
of the time spent on all projects for that client, and at the end of
the
report is a total of all the time spent on all the projects listed on
the
report. So far so good.

At the end of the report, I'd also like to show the average amount of
time
for an individual project. However, all I can conjure up is the average
of
the line items of all the projects, which isn't helpful at all. The
formula
giving me that average is "=Avg([WorkerTime])" and obviously isn't the
formula I need in the Report Footer. So I tried
"=Avg([SubtotalWorkerTime])"
where "SubtotalWorkerTime" is the name of the text box with the total
time
worked for a project, but that yielded only a prompt for
SubtotalWorkerTime
upon opening the report.

I've played around in queries trying to find a way to get one to
calculate
what I need, but I've been completely unsuccessful.

Does anyone have any advice on how to get the average I seek in my
report?
It's probably something painfully obvious, but I'm just not finding it.

Thanks in advance for any help.

 

[Jeff Boyce]

Sorry, I wasn't explicit enough.

What fields are in your tables (field names, data type, contents)? That is
the data structure.

Do you have more than one table? How are the tables related?

I need a better picture of where the data is coming from (i.e., the
structure) to offer specific suggestions on how to do something with it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

To answer your first inquiry: for the sake of this discussion, yes, each
ad
type for a client is a "project."

I'm afraid I don't really know how to answer your questions "How are you
storing this information in your table(s)?" and "What's your table
structure
for this kind of data?" My tables have fields with the data in them. If
there's another way to store data in Access, I don't know it. I'm very
much a
beginner in the program.

So, you're (not) saying that each ad-type for each company is a
"project".
How are you storing this information in your table(s)? I ask because how
you get data/information back out depends heavily on how you have it
stored.

What's your table structure for this kind of data?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is a rather simplified example:

ABC Company

Newspaper Ad
Joe's time 5hrs
Frank's time 7hrs
Judy's time 3hrs
Newspaper Ad total time 15hrs

Magazine Ad
Judy's time 12hrs
Sarah's time 4hrs
Frank's time 10hrs
Bob's time 7hrs
Marsha's time 1hr
Magazine Ad total time 34hrs


ABC Company total time 49hrs

Company 2
Newspaper Ad
Joe's time 11hrs
Judy's time 1hr
Newspaper Ad total time 12hrs

Magazine Ad
Sarah's time 5hrs
Bob's time 1hr
Marsha's time 1hr
Magazine Ad total time 7hrs

Mail Ad
Joe's time 6hrs
Mail Ad total time 6hrs

Company 2 total time 25hrs

Report's total time on ads 74hrs
Average time per project 14.8hrs

--------------------

The current worthless average showing on report is 5.3hrs (the average
amount of time any worker spent on any project: 74hrs/14entries). I
don't
care about that number. What I want to know is on average how much time
is
spent on a complete project (74hrs/5projects). I think the average time
spent
on each client would also be useful data (74hrs/2clients). However, I
can't
figure out how to get either of these averages to calculate on my
report.

Does that make it clearer? Any suggestions?


:

I'm not clear on what you are trying to compute the average of. But
if
the
numbers you seek to average are totals (or subtotals) of other
numbers,
or
if they are themselves already averages, you'll want to carefully
consider
how you are doing the calculation.

If the totals (or averages) have the same number of elements in EVERY
value,
an average makes sense. If the totals or averages you seek to average
have
differing numbers of elements, taking THEIR average makes NO sense.

A recovering statistician...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've created a report to show the amount of time that is spent on
each
project my department works on. It's grouped first by client and
then
by
individual project. Each individual project is broken down into how
much
time
was spent by each employee on the project. At the end of the project
is
a
total of the time spent on that project, at the end of each client
is a
total
of the time spent on all projects for that client, and at the end of
the
report is a total of all the time spent on all the projects listed
on
the
report. So far so good.

At the end of the report, I'd also like to show the average amount
of
time
for an individual project. However, all I can conjure up is the
average
of
the line items of all the projects, which isn't helpful at all. The
formula
giving me that average is "=Avg([WorkerTime])" and obviously isn't
the
formula I need in the Report Footer. So I tried
"=Avg([SubtotalWorkerTime])"
where "SubtotalWorkerTime" is the name of the text box with the
total
time
worked for a project, but that yielded only a prompt for
SubtotalWorkerTime
upon opening the report.

I've played around in queries trying to find a way to get one to
calculate
what I need, but I've been completely unsuccessful.

Does anyone have any advice on how to get the average I seek in my
report?
It's probably something painfully obvious, but I'm just not finding
it.

Thanks in advance for any help.

 

[jkatj]

The report is based on a query with these fields:
ClientName = the name of the client, text field
Worker = name of employee doing work, text field
WorkDone = type of work (editorial, graphic, layout, etc.), text field
WorkerTime = amount of time spent on work, number field
ProjectDate = projects due/completion date, date/time field
ProjectNumber = unique code applied to the project, text field

There are about 30 other fields in the table the query is based on, but none
are relevant to the report. There are also a few other tables, but they also
have no relation to the report I'm working on.

Also, in case this helps explain the structure at all -- a particular
project can have multiple records. For example, each person working on a
project will have a separate record with the same ProjectNumber but a
different name in Worker and different type of work specified in WorkDone. If
a particular person does more than one type of work on the project, he will
have more than one record: one for his editorial time and one for his graphic
design time.

I hope this covers what you were asking for. And I really appreciate you
taking time to help me.

Sorry, I wasn't explicit enough.

What fields are in your tables (field names, data type, contents)? That is
the data structure.

Do you have more than one table? How are the tables related?

I need a better picture of where the data is coming from (i.e., the
structure) to offer specific suggestions on how to do something with it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

To answer your first inquiry: for the sake of this discussion, yes, each
ad
type for a client is a "project."

I'm afraid I don't really know how to answer your questions "How are you
storing this information in your table(s)?" and "What's your table
structure
for this kind of data?" My tables have fields with the data in them. If
there's another way to store data in Access, I don't know it. I'm very
much a
beginner in the program.

So, you're (not) saying that each ad-type for each company is a
"project".
How are you storing this information in your table(s)? I ask because how
you get data/information back out depends heavily on how you have it
stored.

What's your table structure for this kind of data?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Here is a rather simplified example:

ABC Company

Newspaper Ad
Joe's time 5hrs
Frank's time 7hrs
Judy's time 3hrs
Newspaper Ad total time 15hrs

Magazine Ad
Judy's time 12hrs
Sarah's time 4hrs
Frank's time 10hrs
Bob's time 7hrs
Marsha's time 1hr
Magazine Ad total time 34hrs


ABC Company total time 49hrs

Company 2
Newspaper Ad
Joe's time 11hrs
Judy's time 1hr
Newspaper Ad total time 12hrs

Magazine Ad
Sarah's time 5hrs
Bob's time 1hr
Marsha's time 1hr
Magazine Ad total time 7hrs

Mail Ad
Joe's time 6hrs
Mail Ad total time 6hrs

Company 2 total time 25hrs

Report's total time on ads 74hrs
Average time per project 14.8hrs

--------------------

The current worthless average showing on report is 5.3hrs (the average
amount of time any worker spent on any project: 74hrs/14entries). I
don't
care about that number. What I want to know is on average how much time
is
spent on a complete project (74hrs/5projects). I think the average time
spent
on each client would also be useful data (74hrs/2clients). However, I
can't
figure out how to get either of these averages to calculate on my
report.

Does that make it clearer? Any suggestions?


:

I'm not clear on what you are trying to compute the average of. But
if
the
numbers you seek to average are totals (or subtotals) of other
numbers,
or
if they are themselves already averages, you'll want to carefully
consider
how you are doing the calculation.

If the totals (or averages) have the same number of elements in EVERY
value,
an average makes sense. If the totals or averages you seek to average
have
differing numbers of elements, taking THEIR average makes NO sense.

A recovering statistician...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've created a report to show the amount of time that is spent on
each
project my department works on. It's grouped first by client and
then
by
individual project. Each individual project is broken down into how
much
time
was spent by each employee on the project. At the end of the project
is
a
total of the time spent on that project, at the end of each client
is a
total
of the time spent on all projects for that client, and at the end of
the
report is a total of all the time spent on all the projects listed
on
the
report. So far so good.

At the end of the report, I'd also like to show the average amount
of
time
for an individual project. However, all I can conjure up is the
average
of
the line items of all the projects, which isn't helpful at all. The
formula
giving me that average is "=Avg([WorkerTime])" and obviously isn't
the
formula I need in the Report Footer. So I tried
"=Avg([SubtotalWorkerTime])"
where "SubtotalWorkerTime" is the name of the text box with the
total
time
worked for a project, but that yielded only a prompt for
SubtotalWorkerTime
upon opening the report.

I've played around in queries trying to find a way to get one to
calculate
what I need, but I've been completely unsuccessful.

Does anyone have any advice on how to get the average I seek in my
report?
It's probably something painfully obvious, but I'm just not finding
it.

Thanks in advance for any help.

 

[Jeff Boyce]

I think I can see it, now.

The reason your =Avg([SubtotalWorkerTime]) only caused a prompt is because
that is NOT one of the data fields you are bringing into the report.

One way to do this would be to "spell out" the full calculation in your
overall/project average rather than trying to "point" to the textbox control
in which you calculate the [SubtotalWorkerTime].

Another approach might be to use a DAvg() function as a source for that
project average.

Regards

Jeff Boyce
Microsoft Office/Access MVP

The report is based on a query with these fields:
ClientName = the name of the client, text field
Worker = name of employee doing work, text field
WorkDone = type of work (editorial, graphic, layout, etc.), text field
WorkerTime = amount of time spent on work, number field
ProjectDate = projects due/completion date, date/time field
ProjectNumber = unique code applied to the project, text field

There are about 30 other fields in the table the query is based on, but
none
are relevant to the report. There are also a few other tables, but they
also
have no relation to the report I'm working on.

Also, in case this helps explain the structure at all -- a particular
project can have multiple records. For example, each person working on a
project will have a separate record with the same ProjectNumber but a
different name in Worker and different type of work specified in WorkDone.
If
a particular person does more than one type of work on the project, he
will
have more than one record: one for his editorial time and one for his
graphic
design time.

I hope this covers what you were asking for. And I really appreciate you
taking time to help me.

Sorry, I wasn't explicit enough.

What fields are in your tables (field names, data type, contents)? That
is
the data structure.

Do you have more than one table? How are the tables related?

I need a better picture of where the data is coming from (i.e., the
structure) to offer specific suggestions on how to do something with it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

To answer your first inquiry: for the sake of this discussion, yes,
each
ad
type for a client is a "project."

I'm afraid I don't really know how to answer your questions "How are
you
storing this information in your table(s)?" and "What's your table
structure
for this kind of data?" My tables have fields with the data in them. If
there's another way to store data in Access, I don't know it. I'm very
much a
beginner in the program.


:

So, you're (not) saying that each ad-type for each company is a
"project".
How are you storing this information in your table(s)? I ask because
how
you get data/information back out depends heavily on how you have it
stored.

What's your table structure for this kind of data?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Here is a rather simplified example:

ABC Company

Newspaper Ad
Joe's time 5hrs
Frank's time 7hrs
Judy's time 3hrs
Newspaper Ad total time 15hrs

Magazine Ad
Judy's time 12hrs
Sarah's time 4hrs
Frank's time 10hrs
Bob's time 7hrs
Marsha's time 1hr
Magazine Ad total time 34hrs


ABC Company total time 49hrs

Company 2
Newspaper Ad
Joe's time 11hrs
Judy's time 1hr
Newspaper Ad total time 12hrs

Magazine Ad
Sarah's time 5hrs
Bob's time 1hr
Marsha's time 1hr
Magazine Ad total time 7hrs

Mail Ad
Joe's time 6hrs
Mail Ad total time 6hrs

Company 2 total time 25hrs

Report's total time on ads 74hrs
Average time per project 14.8hrs

--------------------

The current worthless average showing on report is 5.3hrs (the
average
amount of time any worker spent on any project: 74hrs/14entries). I
don't
care about that number. What I want to know is on average how much
time
is
spent on a complete project (74hrs/5projects). I think the average
time
spent
on each client would also be useful data (74hrs/2clients). However,
I
can't
figure out how to get either of these averages to calculate on my
report.

Does that make it clearer? Any suggestions?


:

I'm not clear on what you are trying to compute the average of.
But
if
the
numbers you seek to average are totals (or subtotals) of other
numbers,
or
if they are themselves already averages, you'll want to carefully
consider
how you are doing the calculation.

If the totals (or averages) have the same number of elements in
EVERY
value,
an average makes sense. If the totals or averages you seek to
average
have
differing numbers of elements, taking THEIR average makes NO sense.

A recovering statistician...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've created a report to show the amount of time that is spent on
each
project my department works on. It's grouped first by client and
then
by
individual project. Each individual project is broken down into
how
much
time
was spent by each employee on the project. At the end of the
project
is
a
total of the time spent on that project, at the end of each
client
is a
total
of the time spent on all projects for that client, and at the end
of
the
report is a total of all the time spent on all the projects
listed
on
the
report. So far so good.

At the end of the report, I'd also like to show the average
amount
of
time
for an individual project. However, all I can conjure up is the
average
of
the line items of all the projects, which isn't helpful at all.
The
formula
giving me that average is "=Avg([WorkerTime])" and obviously
isn't
the
formula I need in the Report Footer. So I tried
"=Avg([SubtotalWorkerTime])"
where "SubtotalWorkerTime" is the name of the text box with the
total
time
worked for a project, but that yielded only a prompt for
SubtotalWorkerTime
upon opening the report.

I've played around in queries trying to find a way to get one to
calculate
what I need, but I've been completely unsuccessful.

Does anyone have any advice on how to get the average I seek in
my
report?
It's probably something painfully obvious, but I'm just not
finding
it.

Thanks in advance for any help.

 

[jkatj]

I couldn't figure how to make DAvg work for my particular needs; however,
your suggestion to spell out the full calculation got me on a path that
ultimately worked. By putting a running sum in a text box in the footer for
each project grouping (which is called [ProjectCount]), I was able create a
formula in the report footer using the running sum that gives me the average
I've been working toward:

=Sum([WorkerTime])/[ProjectCount]

I appreciate all your time, advice, and patience.

I think I can see it, now.

The reason your =Avg([SubtotalWorkerTime]) only caused a prompt is because
that is NOT one of the data fields you are bringing into the report.

One way to do this would be to "spell out" the full calculation in your
overall/project average rather than trying to "point" to the textbox control
in which you calculate the [SubtotalWorkerTime].

Another approach might be to use a DAvg() function as a source for that
project average.

Regards

Jeff Boyce
Microsoft Office/Access MVP

The report is based on a query with these fields:
ClientName = the name of the client, text field
Worker = name of employee doing work, text field
WorkDone = type of work (editorial, graphic, layout, etc.), text field
WorkerTime = amount of time spent on work, number field
ProjectDate = projects due/completion date, date/time field
ProjectNumber = unique code applied to the project, text field

There are about 30 other fields in the table the query is based on, but
none
are relevant to the report. There are also a few other tables, but they
also
have no relation to the report I'm working on.

Also, in case this helps explain the structure at all -- a particular
project can have multiple records. For example, each person working on a
project will have a separate record with the same ProjectNumber but a
different name in Worker and different type of work specified in WorkDone.
If
a particular person does more than one type of work on the project, he
will
have more than one record: one for his editorial time and one for his
graphic
design time.

I hope this covers what you were asking for. And I really appreciate you
taking time to help me.

Sorry, I wasn't explicit enough.

What fields are in your tables (field names, data type, contents)? That
is
the data structure.

Do you have more than one table? How are the tables related?

I need a better picture of where the data is coming from (i.e., the
structure) to offer specific suggestions on how to do something with it.

Regards

Jeff Boyce
Microsoft Office/Access MVP


To answer your first inquiry: for the sake of this discussion, yes,
each
ad
type for a client is a "project."

I'm afraid I don't really know how to answer your questions "How are
you
storing this information in your table(s)?" and "What's your table
structure
for this kind of data?" My tables have fields with the data in them. If
there's another way to store data in Access, I don't know it. I'm very
much a
beginner in the program.


:

So, you're (not) saying that each ad-type for each company is a
"project".
How are you storing this information in your table(s)? I ask because
how
you get data/information back out depends heavily on how you have it
stored.

What's your table structure for this kind of data?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Here is a rather simplified example:

ABC Company

Newspaper Ad
Joe's time 5hrs
Frank's time 7hrs
Judy's time 3hrs
Newspaper Ad total time 15hrs

Magazine Ad
Judy's time 12hrs
Sarah's time 4hrs
Frank's time 10hrs
Bob's time 7hrs
Marsha's time 1hr
Magazine Ad total time 34hrs


ABC Company total time 49hrs

Company 2
Newspaper Ad
Joe's time 11hrs
Judy's time 1hr
Newspaper Ad total time 12hrs

Magazine Ad
Sarah's time 5hrs
Bob's time 1hr
Marsha's time 1hr
Magazine Ad total time 7hrs

Mail Ad
Joe's time 6hrs
Mail Ad total time 6hrs

Company 2 total time 25hrs

Report's total time on ads 74hrs
Average time per project 14.8hrs

--------------------

The current worthless average showing on report is 5.3hrs (the
average
amount of time any worker spent on any project: 74hrs/14entries). I
don't
care about that number. What I want to know is on average how much
time
is
spent on a complete project (74hrs/5projects). I think the average
time
spent
on each client would also be useful data (74hrs/2clients). However,
I
can't
figure out how to get either of these averages to calculate on my
report.

Does that make it clearer? Any suggestions?


:

I'm not clear on what you are trying to compute the average of.
But
if
the
numbers you seek to average are totals (or subtotals) of other
numbers,
or
if they are themselves already averages, you'll want to carefully
consider
how you are doing the calculation.

If the totals (or averages) have the same number of elements in
EVERY
value,
an average makes sense. If the totals or averages you seek to
average
have
differing numbers of elements, taking THEIR average makes NO sense.

A recovering statistician...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've created a report to show the amount of time that is spent on
each
project my department works on. It's grouped first by client and
then
by
individual project. Each individual project is broken down into
how
much
time
was spent by each employee on the project. At the end of the
project
is
a
total of the time spent on that project, at the end of each
client
is a
total
of the time spent on all projects for that client, and at the end
of
the
report is a total of all the time spent on all the projects
listed
on
the
report. So far so good.

At the end of the report, I'd also like to show the average
amount
of
time
for an individual project. However, all I can conjure up is the
average
of
the line items of all the projects, which isn't helpful at all.
The
formula
giving me that average is "=Avg([WorkerTime])" and obviously
isn't
the
formula I need in the Report Footer. So I tried
"=Avg([SubtotalWorkerTime])"
where "SubtotalWorkerTime" is the name of the text box with the
total
time
worked for a project, but that yielded only a prompt for
SubtotalWorkerTime
upon opening the report.

I've played around in queries trying to find a way to get one to
calculate
what I need, but I've been completely unsuccessful.

Does anyone have any advice on how to get the average I seek in
my
report?
It's probably something painfully obvious, but I'm just not
finding
it.

Thanks in advance for any help.