Average of averages

[Guest]

Hopefully this post doesn't get too long but I want to give all the info I
can.

BACKGROUND: I have an DB with 1 table that keeps track of 1/2 call
increments showing calls taken, # coming in, % that go to voice mail, etc. I
developed a report from a Total Query that groups on the time intervals and
Averages some of different fields. The table name is All Calls (if you're
looking at the SQL below).

Although I used Design View to make it, here's the SQL for the query (if
you're interested or it will help). Sorry if it gets too messy with the AS
pieces. I'm sure it came from the "renaming" I did to make the fields more
intuitive as I worked on the report.

SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Avg([All Calls].[Avg ACD Time]) AS [Avg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Avg([All Calls].[Avg Speed Ans]) AS [Avg
Ans Speed], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Avg([All
Calls].[Aban Calls]) AS [Aban Calls], Avg([% Aband Calls]/100) AS [% Aban],
Avg([All Calls].[Flow Out]) AS [Voice Mail Calls], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Avg([All Calls].[Avg Pos Staff]) AS [Avg Pos
Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];


PROBLEM: The report displays my fields (which are essentially averages)
just fine in the Details section. Now I would like to have an overall
Average displayed. I keep getting a #Error when the report is run.

The MS site I found
http://office.microsoft.com/en-us/assistance/HA011814481033.aspx
seems to indicate it's not recognizing which field to Average. I've tried
renaming fields on the report with underscores between words. For example,
one of the fields is [ACD Calls]. Using the properties of the control, I
changed the name to ACD_Calls. Then in my header, I tried a control using
=Avg([ACD_Calls]) thinking with a different name, it could recognize it.
That still gives me #Error. By using the underscore in the name, I was
hoping I took out all possibilities of the circular reference as well (as
indicated in the article).

Any other suggestions? I would think with all Access can do it should be
capable of figuring an Average of Averages.

If you need some further/better explanation of what I'm trying to do or
would like to see the DB I have, let me know.
Thanks so much for any insight.
Marcia

 

[Immanuel Sibero]

Hi Marcia
I hope I'm understanding you correctly. I have not tried this, but maybe you
should try a different approach in calculating this "average of averages".
Let's say:

Calls 4 6 4
Days 2 2 2
Avg 2 3 2

There are at least two ways to calculate Avg of Averages:

A. (2+3+2)/3 = 2.33
B. (4+6+4)/(2+2+2) = 2.33

In a *group* query, I think A. may not work but B. works. So in your query,
define a column with the SUM of your calls, and another column with the
COUNT of your calls and get your Ave of Averages by dividing SUM by the
COUNT.

Also note that your Ave of Averages is a simple average, not weighted
average. I dont know if that matters to you.


HTH,
Immanuel Sibero

Hopefully this post doesn't get too long but I want to give all the info I
can.

BACKGROUND: I have an DB with 1 table that keeps track of 1/2 call
increments showing calls taken, # coming in, % that go to voice mail, etc. I
developed a report from a Total Query that groups on the time intervals and
Averages some of different fields. The table name is All Calls (if you're
looking at the SQL below).

Although I used Design View to make it, here's the SQL for the query (if
you're interested or it will help). Sorry if it gets too messy with the AS
pieces. I'm sure it came from the "renaming" I did to make the fields more
intuitive as I worked on the report.

SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Avg([All Calls].[Avg ACD Time]) AS [Avg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Avg([All Calls].[Avg Speed Ans]) AS [Avg
Ans Speed], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Avg([All
Calls].[Aban Calls]) AS [Aban Calls], Avg([% Aband Calls]/100) AS [% Aban],
Avg([All Calls].[Flow Out]) AS [Voice Mail Calls], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Avg([All Calls].[Avg Pos Staff]) AS [Avg Pos
Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];


PROBLEM: The report displays my fields (which are essentially averages)
just fine in the Details section. Now I would like to have an overall
Average displayed. I keep getting a #Error when the report is run.

The MS site I found
http://office.microsoft.com/en-us/assistance/HA011814481033.aspx
seems to indicate it's not recognizing which field to Average. I've tried
renaming fields on the report with underscores between words. For example,
one of the fields is [ACD Calls]. Using the properties of the control, I
changed the name to ACD_Calls. Then in my header, I tried a control using
=Avg([ACD_Calls]) thinking with a different name, it could recognize it.
That still gives me #Error. By using the underscore in the name, I was
hoping I took out all possibilities of the circular reference as well (as
indicated in the article).

Any other suggestions? I would think with all Access can do it should be
capable of figuring an Average of Averages.

If you need some further/better explanation of what I'm trying to do or
would like to see the DB I have, let me know.
Thanks so much for any insight.
Marcia

 

[Jeff Boyce]

This may just be repeating what Immanuel mentioned, but you REALLY don't
want to use an Average of Averages -- take it from a recovering statistics
instructor!

Because each Average can be based on a different "base", the only
"legitimate" way to average your overall data is to ... average your overall
data (and not compute an Average of Averages). Add up EVERYTHING, and
divide by the total count.

Good luck

Jeff Boyce
<Access MVP>

Hopefully this post doesn't get too long but I want to give all the info I
can.

BACKGROUND: I have an DB with 1 table that keeps track of 1/2 call
increments showing calls taken, # coming in, % that go to voice mail, etc.
I
developed a report from a Total Query that groups on the time intervals
and
Averages some of different fields. The table name is All Calls (if you're
looking at the SQL below).

Although I used Design View to make it, here's the SQL for the query (if
you're interested or it will help). Sorry if it gets too messy with the
AS
pieces. I'm sure it came from the "renaming" I did to make the fields
more
intuitive as I worked on the report.

SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Avg([All Calls].[Avg ACD Time]) AS [Avg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Avg([All Calls].[Avg Speed Ans]) AS
[Avg
Ans Speed], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Avg([All
Calls].[Aban Calls]) AS [Aban Calls], Avg([% Aband Calls]/100) AS [%
Aban],
Avg([All Calls].[Flow Out]) AS [Voice Mail Calls], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Avg([All Calls].[Avg Pos Staff]) AS [Avg
Pos
Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];


PROBLEM: The report displays my fields (which are essentially averages)
just fine in the Details section. Now I would like to have an overall
Average displayed. I keep getting a #Error when the report is run.

The MS site I found
http://office.microsoft.com/en-us/assistance/HA011814481033.aspx
seems to indicate it's not recognizing which field to Average. I've tried
renaming fields on the report with underscores between words. For
example,
one of the fields is [ACD Calls]. Using the properties of the control, I
changed the name to ACD_Calls. Then in my header, I tried a control using
=Avg([ACD_Calls]) thinking with a different name, it could recognize it.
That still gives me #Error. By using the underscore in the name, I was
hoping I took out all possibilities of the circular reference as well (as
indicated in the article).

Any other suggestions? I would think with all Access can do it should be
capable of figuring an Average of Averages.

If you need some further/better explanation of what I'm trying to do or
would like to see the DB I have, let me know.
Thanks so much for any insight.
Marcia

 

[Immanuel Sibero]

.. a recovering statistics
instructor!

Was it that bad?

This may just be repeating what Immanuel mentioned, but you REALLY don't
want to use an Average of Averages -- take it from a recovering statistics
instructor!

Because each Average can be based on a different "base", the only
"legitimate" way to average your overall data is to ... average your overall
data (and not compute an Average of Averages). Add up EVERYTHING, and
divide by the total count.

Good luck

Jeff Boyce
<Access MVP>

Hopefully this post doesn't get too long but I want to give all the info I
can.

BACKGROUND: I have an DB with 1 table that keeps track of 1/2 call
increments showing calls taken, # coming in, % that go to voice mail, etc.
I
developed a report from a Total Query that groups on the time intervals
and
Averages some of different fields. The table name is All Calls (if you're
looking at the SQL below).

Although I used Design View to make it, here's the SQL for the query (if
you're interested or it will help). Sorry if it gets too messy with the
AS
pieces. I'm sure it came from the "renaming" I did to make the fields
more
intuitive as I worked on the report.

SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Avg([All Calls].[Avg ACD Time]) AS [Avg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Avg([All Calls].[Avg Speed Ans]) AS
[Avg
Ans Speed], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Avg([All
Calls].[Aban Calls]) AS [Aban Calls], Avg([% Aband Calls]/100) AS [%
Aban],
Avg([All Calls].[Flow Out]) AS [Voice Mail Calls], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Avg([All Calls].[Avg Pos Staff]) AS [Avg
Pos
Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];


PROBLEM: The report displays my fields (which are essentially averages)
just fine in the Details section. Now I would like to have an overall
Average displayed. I keep getting a #Error when the report is run.

The MS site I found
http://office.microsoft.com/en-us/assistance/HA011814481033.aspx
seems to indicate it's not recognizing which field to Average. I've tried
renaming fields on the report with underscores between words. For
example,
one of the fields is [ACD Calls]. Using the properties of the control, I
changed the name to ACD_Calls. Then in my header, I tried a control using
=Avg([ACD_Calls]) thinking with a different name, it could recognize it.
That still gives me #Error. By using the underscore in the name, I was
hoping I took out all possibilities of the circular reference as well (as
indicated in the article).

Any other suggestions? I would think with all Access can do it should be
capable of figuring an Average of Averages.

If you need some further/better explanation of what I'm trying to do or
would like to see the DB I have, let me know.
Thanks so much for any insight.
Marcia

 

[Jeff Boyce]

Near as I can tell, it's the kind of experience you never get over <g>

Jeff

.. a recovering statistics
instructor!

Was it that bad?

This may just be repeating what Immanuel mentioned, but you REALLY don't
want to use an Average of Averages -- take it from a recovering
statistics
instructor!

Because each Average can be based on a different "base", the only
"legitimate" way to average your overall data is to ... average your overall
data (and not compute an Average of Averages). Add up EVERYTHING, and
divide by the total count.

Good luck

Jeff Boyce
<Access MVP>

Hopefully this post doesn't get too long but I want to give all the
info I
can.

BACKGROUND: I have an DB with 1 table that keeps track of 1/2 call
increments showing calls taken, # coming in, % that go to voice mail, etc.
I
developed a report from a Total Query that groups on the time intervals
and
Averages some of different fields. The table name is All Calls (if you're
looking at the SQL below).

Although I used Design View to make it, here's the SQL for the query
(if
you're interested or it will help). Sorry if it gets too messy with
the
AS
pieces. I'm sure it came from the "renaming" I did to make the fields
more
intuitive as I worked on the report.

SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Avg([All Calls].[Avg ACD Time]) AS [Avg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Avg([All Calls].[Avg Speed Ans]) AS
[Avg
Ans Speed], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Avg([All
Calls].[Aban Calls]) AS [Aban Calls], Avg([% Aband Calls]/100) AS [%
Aban],
Avg([All Calls].[Flow Out]) AS [Voice Mail Calls], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Avg([All Calls].[Avg Pos Staff]) AS [Avg
Pos
Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];


PROBLEM: The report displays my fields (which are essentially
averages)
just fine in the Details section. Now I would like to have an overall
Average displayed. I keep getting a #Error when the report is run.

The MS site I found
http://office.microsoft.com/en-us/assistance/HA011814481033.aspx
seems to indicate it's not recognizing which field to Average. I've tried
renaming fields on the report with underscores between words. For
example,
one of the fields is [ACD Calls]. Using the properties of the control, I
changed the name to ACD_Calls. Then in my header, I tried a control using
=Avg([ACD_Calls]) thinking with a different name, it could recognize
it.
That still gives me #Error. By using the underscore in the name, I was
hoping I took out all possibilities of the circular reference as well (as
indicated in the article).

Any other suggestions? I would think with all Access can do it should be
capable of figuring an Average of Averages.

If you need some further/better explanation of what I'm trying to do or
would like to see the DB I have, let me know.
Thanks so much for any insight.
Marcia

 

[Guest]

Thanks for the great advice. It's clear the "obvious" escaped me as I tried
to solve this problem. Thanks again!
Marcia

This may just be repeating what Immanuel mentioned, but you REALLY don't
want to use an Average of Averages -- take it from a recovering statistics
instructor!

Because each Average can be based on a different "base", the only
"legitimate" way to average your overall data is to ... average your overall
data (and not compute an Average of Averages). Add up EVERYTHING, and
divide by the total count.

Good luck

Jeff Boyce
<Access MVP>

Hopefully this post doesn't get too long but I want to give all the info I
can.

BACKGROUND: I have an DB with 1 table that keeps track of 1/2 call
increments showing calls taken, # coming in, % that go to voice mail, etc.
I
developed a report from a Total Query that groups on the time intervals
and
Averages some of different fields. The table name is All Calls (if you're
looking at the SQL below).

Although I used Design View to make it, here's the SQL for the query (if
you're interested or it will help). Sorry if it gets too messy with the
AS
pieces. I'm sure it came from the "renaming" I did to make the fields
more
intuitive as I worked on the report.

SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Avg([All Calls].[Avg ACD Time]) AS [Avg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Avg([All Calls].[Avg Speed Ans]) AS
[Avg
Ans Speed], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Avg([All
Calls].[Aban Calls]) AS [Aban Calls], Avg([% Aband Calls]/100) AS [%
Aban],
Avg([All Calls].[Flow Out]) AS [Voice Mail Calls], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Avg([All Calls].[Avg Pos Staff]) AS [Avg
Pos
Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];


PROBLEM: The report displays my fields (which are essentially averages)
just fine in the Details section. Now I would like to have an overall
Average displayed. I keep getting a #Error when the report is run.

The MS site I found
http://office.microsoft.com/en-us/assistance/HA011814481033.aspx
seems to indicate it's not recognizing which field to Average. I've tried
renaming fields on the report with underscores between words. For
example,
one of the fields is [ACD Calls]. Using the properties of the control, I
changed the name to ACD_Calls. Then in my header, I tried a control using
=Avg([ACD_Calls]) thinking with a different name, it could recognize it.
That still gives me #Error. By using the underscore in the name, I was
hoping I took out all possibilities of the circular reference as well (as
indicated in the article).

Any other suggestions? I would think with all Access can do it should be
capable of figuring an Average of Averages.

If you need some further/better explanation of what I'm trying to do or
would like to see the DB I have, let me know.
Thanks so much for any insight.
Marcia

 

[Guest]

Thanks for the great advice. It's clear the "obvious" escaped me as I tried
to solve this problem. Thanks again!
Marcia

Hi Marcia
I hope I'm understanding you correctly. I have not tried this, but maybe you
should try a different approach in calculating this "average of averages".
Let's say:

Calls 4 6 4
Days 2 2 2
Avg 2 3 2

There are at least two ways to calculate Avg of Averages:

A. (2+3+2)/3 = 2.33
B. (4+6+4)/(2+2+2) = 2.33

In a *group* query, I think A. may not work but B. works. So in your query,
define a column with the SUM of your calls, and another column with the
COUNT of your calls and get your Ave of Averages by dividing SUM by the
COUNT.

Also note that your Ave of Averages is a simple average, not weighted
average. I dont know if that matters to you.


HTH,
Immanuel Sibero

Hopefully this post doesn't get too long but I want to give all the info I
can.

BACKGROUND: I have an DB with 1 table that keeps track of 1/2 call
increments showing calls taken, # coming in, % that go to voice mail, etc. I
developed a report from a Total Query that groups on the time intervals and
Averages some of different fields. The table name is All Calls (if you're
looking at the SQL below).

Although I used Design View to make it, here's the SQL for the query (if
you're interested or it will help). Sorry if it gets too messy with the AS
pieces. I'm sure it came from the "renaming" I did to make the fields more
intuitive as I worked on the report.

SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Avg([All Calls].[Avg ACD Time]) AS [Avg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Avg([All Calls].[Avg Speed Ans]) AS [Avg
Ans Speed], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Avg([All
Calls].[Aban Calls]) AS [Aban Calls], Avg([% Aband Calls]/100) AS [% Aban],
Avg([All Calls].[Flow Out]) AS [Voice Mail Calls], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Avg([All Calls].[Avg Pos Staff]) AS [Avg Pos
Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];


PROBLEM: The report displays my fields (which are essentially averages)
just fine in the Details section. Now I would like to have an overall
Average displayed. I keep getting a #Error when the report is run.

The MS site I found
http://office.microsoft.com/en-us/assistance/HA011814481033.aspx
seems to indicate it's not recognizing which field to Average. I've tried
renaming fields on the report with underscores between words. For example,
one of the fields is [ACD Calls]. Using the properties of the control, I
changed the name to ACD_Calls. Then in my header, I tried a control using
=Avg([ACD_Calls]) thinking with a different name, it could recognize it.
That still gives me #Error. By using the underscore in the name, I was
hoping I took out all possibilities of the circular reference as well (as
indicated in the article).

Any other suggestions? I would think with all Access can do it should be
capable of figuring an Average of Averages.

If you need some further/better explanation of what I'm trying to do or
would like to see the DB I have, let me know.
Thanks so much for any insight.
Marcia

 

[Guest]

Okay, I'm still in a dilemma. I added 2 columns for each of fields - one to
total the # and one to count. On my report I then divided Total/Count.
Because my query is actually grouping on one of the fields, I essentially
just get the same average as the first "group". Do you know any other way
around this?

This is what my query looks like now:
SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Sum([All Calls].[ACD Calls]) AS [SumOfACD Calls], Count([All
Calls].[ACD Calls]) AS [CountOfACD Calls], Avg([All Calls].[Avg ACD Time]) AS
[Avg ACD Time], Sum([All Calls].[Avg ACD Time]) AS [SumOfAvg ACD Time],
Count([All Calls].[Avg ACD Time]) AS [CountOfAvg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Sum([All Calls].[Max Delay]) AS [SumOfMax
Delay], Count([All Calls].[Max Delay]) AS [CountOfMax Delay], Avg([All
Calls].[Avg Speed Ans]) AS [Avg Ans Speed], Sum([All Calls].[Avg Speed Ans])
AS [SumOfAvg Speed Ans], Count([All Calls].[Avg Speed Ans]) AS [CountOfAvg
Speed Ans], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Sum([All
Calls].[Avg Aban Time]) AS [SumOfAvg Aban Time], Count([All Calls].[Avg Aban
Time]) AS [CountOfAvg Aban Time], Avg([All Calls].[Aban Calls]) AS [Aban
Calls], Sum([All Calls].[Aban Calls]) AS [SumOfAban Calls], Count([All
Calls].[Aban Calls]) AS [CountOfAban Calls], Avg([% Aband Calls]/100) AS [%
Aban], Sum([All Calls].[% Aband Calls]) AS [SumOf% Aband Calls], Count([All
Calls].[% Aband Calls]) AS [CountOf% Aband Calls], Avg([All Calls].[Flow
Out]) AS [Voice Mail Calls], Sum([All Calls].[Flow Out]) AS [SumOf Voice
Mail], Count([All Calls].[Flow Out]) AS [CountOf Voice Mail], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Sum([All Calls].[% Flow Out Calls]) AS [SumOf
%Voice Mail], Count([All Calls].[% Flow Out Calls]) AS [CountOf %Voice Mail],
Avg([All Calls].[Avg Pos Staff]) AS [Avg Pos Staff], Sum([All Calls].[Avg Pos
Staff]) AS [SumOfAvg Pos Staff], Count([All Calls].[Avg Pos Staff]) AS
[CountOfAvg Pos Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];

This may just be repeating what Immanuel mentioned, but you REALLY don't
want to use an Average of Averages -- take it from a recovering statistics
instructor!

Because each Average can be based on a different "base", the only
"legitimate" way to average your overall data is to ... average your overall
data (and not compute an Average of Averages). Add up EVERYTHING, and
divide by the total count.

Good luck

Jeff Boyce
<Access MVP>

Hopefully this post doesn't get too long but I want to give all the info I
can.

BACKGROUND: I have an DB with 1 table that keeps track of 1/2 call
increments showing calls taken, # coming in, % that go to voice mail, etc.
I
developed a report from a Total Query that groups on the time intervals
and
Averages some of different fields. The table name is All Calls (if you're
looking at the SQL below).

Although I used Design View to make it, here's the SQL for the query (if
you're interested or it will help). Sorry if it gets too messy with the
AS
pieces. I'm sure it came from the "renaming" I did to make the fields
more
intuitive as I worked on the report.

SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Avg([All Calls].[Avg ACD Time]) AS [Avg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Avg([All Calls].[Avg Speed Ans]) AS
[Avg
Ans Speed], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Avg([All
Calls].[Aban Calls]) AS [Aban Calls], Avg([% Aband Calls]/100) AS [%
Aban],
Avg([All Calls].[Flow Out]) AS [Voice Mail Calls], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Avg([All Calls].[Avg Pos Staff]) AS [Avg
Pos
Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];


PROBLEM: The report displays my fields (which are essentially averages)
just fine in the Details section. Now I would like to have an overall
Average displayed. I keep getting a #Error when the report is run.

The MS site I found
http://office.microsoft.com/en-us/assistance/HA011814481033.aspx
seems to indicate it's not recognizing which field to Average. I've tried
renaming fields on the report with underscores between words. For
example,
one of the fields is [ACD Calls]. Using the properties of the control, I
changed the name to ACD_Calls. Then in my header, I tried a control using
=Avg([ACD_Calls]) thinking with a different name, it could recognize it.
That still gives me #Error. By using the underscore in the name, I was
hoping I took out all possibilities of the circular reference as well (as
indicated in the article).

Any other suggestions? I would think with all Access can do it should be
capable of figuring an Average of Averages.

If you need some further/better explanation of what I'm trying to do or
would like to see the DB I have, let me know.
Thanks so much for any insight.
Marcia

 

[Guest]

I also tried starting back to basics and not using a Grouping query. I
changed my report to use the original table as source and then added Grouping
to it. For my Cumulative Average, I added a control to take (for example)
=Sum([Calls])/Count([Calls]) but still get the #Error.

I still seem to be "missing the boat" somewhere. Any help would be greatly
appreciated.

Thanks,
Marcia

Okay, I'm still in a dilemma. I added 2 columns for each of fields - one to
total the # and one to count. On my report I then divided Total/Count.
Because my query is actually grouping on one of the fields, I essentially
just get the same average as the first "group". Do you know any other way
around this?

This is what my query looks like now:
SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Sum([All Calls].[ACD Calls]) AS [SumOfACD Calls], Count([All
Calls].[ACD Calls]) AS [CountOfACD Calls], Avg([All Calls].[Avg ACD Time]) AS
[Avg ACD Time], Sum([All Calls].[Avg ACD Time]) AS [SumOfAvg ACD Time],
Count([All Calls].[Avg ACD Time]) AS [CountOfAvg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Sum([All Calls].[Max Delay]) AS [SumOfMax
Delay], Count([All Calls].[Max Delay]) AS [CountOfMax Delay], Avg([All
Calls].[Avg Speed Ans]) AS [Avg Ans Speed], Sum([All Calls].[Avg Speed Ans])
AS [SumOfAvg Speed Ans], Count([All Calls].[Avg Speed Ans]) AS [CountOfAvg
Speed Ans], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Sum([All
Calls].[Avg Aban Time]) AS [SumOfAvg Aban Time], Count([All Calls].[Avg Aban
Time]) AS [CountOfAvg Aban Time], Avg([All Calls].[Aban Calls]) AS [Aban
Calls], Sum([All Calls].[Aban Calls]) AS [SumOfAban Calls], Count([All
Calls].[Aban Calls]) AS [CountOfAban Calls], Avg([% Aband Calls]/100) AS [%
Aban], Sum([All Calls].[% Aband Calls]) AS [SumOf% Aband Calls], Count([All
Calls].[% Aband Calls]) AS [CountOf% Aband Calls], Avg([All Calls].[Flow
Out]) AS [Voice Mail Calls], Sum([All Calls].[Flow Out]) AS [SumOf Voice
Mail], Count([All Calls].[Flow Out]) AS [CountOf Voice Mail], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Sum([All Calls].[% Flow Out Calls]) AS [SumOf
%Voice Mail], Count([All Calls].[% Flow Out Calls]) AS [CountOf %Voice Mail],
Avg([All Calls].[Avg Pos Staff]) AS [Avg Pos Staff], Sum([All Calls].[Avg Pos
Staff]) AS [SumOfAvg Pos Staff], Count([All Calls].[Avg Pos Staff]) AS
[CountOfAvg Pos Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];

This may just be repeating what Immanuel mentioned, but you REALLY don't
want to use an Average of Averages -- take it from a recovering statistics
instructor!

Because each Average can be based on a different "base", the only
"legitimate" way to average your overall data is to ... average your overall
data (and not compute an Average of Averages). Add up EVERYTHING, and
divide by the total count.

Good luck

Jeff Boyce
<Access MVP>

Hopefully this post doesn't get too long but I want to give all the info I
can.

BACKGROUND: I have an DB with 1 table that keeps track of 1/2 call
increments showing calls taken, # coming in, % that go to voice mail, etc.
I
developed a report from a Total Query that groups on the time intervals
and
Averages some of different fields. The table name is All Calls (if you're
looking at the SQL below).

Although I used Design View to make it, here's the SQL for the query (if
you're interested or it will help). Sorry if it gets too messy with the
AS
pieces. I'm sure it came from the "renaming" I did to make the fields
more
intuitive as I worked on the report.

SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Avg([All Calls].[Avg ACD Time]) AS [Avg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Avg([All Calls].[Avg Speed Ans]) AS
[Avg
Ans Speed], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Avg([All
Calls].[Aban Calls]) AS [Aban Calls], Avg([% Aband Calls]/100) AS [%
Aban],
Avg([All Calls].[Flow Out]) AS [Voice Mail Calls], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Avg([All Calls].[Avg Pos Staff]) AS [Avg
Pos
Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];


PROBLEM: The report displays my fields (which are essentially averages)
just fine in the Details section. Now I would like to have an overall
Average displayed. I keep getting a #Error when the report is run.

The MS site I found
http://office.microsoft.com/en-us/assistance/HA011814481033.aspx
seems to indicate it's not recognizing which field to Average. I've tried
renaming fields on the report with underscores between words. For
example,
one of the fields is [ACD Calls]. Using the properties of the control, I
changed the name to ACD_Calls. Then in my header, I tried a control using
=Avg([ACD_Calls]) thinking with a different name, it could recognize it.
That still gives me #Error. By using the underscore in the name, I was
hoping I took out all possibilities of the circular reference as well (as
indicated in the article).

Any other suggestions? I would think with all Access can do it should be
capable of figuring an Average of Averages.

If you need some further/better explanation of what I'm trying to do or
would like to see the DB I have, let me know.
Thanks so much for any insight.
Marcia

 

[Guest]

Ah - ha. The light bulb finally went on and I got it to work. Thanks anyway.
Marcia

I also tried starting back to basics and not using a Grouping query. I
changed my report to use the original table as source and then added Grouping
to it. For my Cumulative Average, I added a control to take (for example)
=Sum([Calls])/Count([Calls]) but still get the #Error.

I still seem to be "missing the boat" somewhere. Any help would be greatly
appreciated.

Thanks,
Marcia

Okay, I'm still in a dilemma. I added 2 columns for each of fields - one to
total the # and one to count. On my report I then divided Total/Count.
Because my query is actually grouping on one of the fields, I essentially
just get the same average as the first "group". Do you know any other way
around this?

This is what my query looks like now:
SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Sum([All Calls].[ACD Calls]) AS [SumOfACD Calls], Count([All
Calls].[ACD Calls]) AS [CountOfACD Calls], Avg([All Calls].[Avg ACD Time]) AS
[Avg ACD Time], Sum([All Calls].[Avg ACD Time]) AS [SumOfAvg ACD Time],
Count([All Calls].[Avg ACD Time]) AS [CountOfAvg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Sum([All Calls].[Max Delay]) AS [SumOfMax
Delay], Count([All Calls].[Max Delay]) AS [CountOfMax Delay], Avg([All
Calls].[Avg Speed Ans]) AS [Avg Ans Speed], Sum([All Calls].[Avg Speed Ans])
AS [SumOfAvg Speed Ans], Count([All Calls].[Avg Speed Ans]) AS [CountOfAvg
Speed Ans], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Sum([All
Calls].[Avg Aban Time]) AS [SumOfAvg Aban Time], Count([All Calls].[Avg Aban
Time]) AS [CountOfAvg Aban Time], Avg([All Calls].[Aban Calls]) AS [Aban
Calls], Sum([All Calls].[Aban Calls]) AS [SumOfAban Calls], Count([All
Calls].[Aban Calls]) AS [CountOfAban Calls], Avg([% Aband Calls]/100) AS [%
Aban], Sum([All Calls].[% Aband Calls]) AS [SumOf% Aband Calls], Count([All
Calls].[% Aband Calls]) AS [CountOf% Aband Calls], Avg([All Calls].[Flow
Out]) AS [Voice Mail Calls], Sum([All Calls].[Flow Out]) AS [SumOf Voice
Mail], Count([All Calls].[Flow Out]) AS [CountOf Voice Mail], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Sum([All Calls].[% Flow Out Calls]) AS [SumOf
%Voice Mail], Count([All Calls].[% Flow Out Calls]) AS [CountOf %Voice Mail],
Avg([All Calls].[Avg Pos Staff]) AS [Avg Pos Staff], Sum([All Calls].[Avg Pos
Staff]) AS [SumOfAvg Pos Staff], Count([All Calls].[Avg Pos Staff]) AS
[CountOfAvg Pos Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];

This may just be repeating what Immanuel mentioned, but you REALLY don't
want to use an Average of Averages -- take it from a recovering statistics
instructor!

Because each Average can be based on a different "base", the only
"legitimate" way to average your overall data is to ... average your overall
data (and not compute an Average of Averages). Add up EVERYTHING, and
divide by the total count.

Good luck

Jeff Boyce
<Access MVP>

Hopefully this post doesn't get too long but I want to give all the info I
can.

BACKGROUND: I have an DB with 1 table that keeps track of 1/2 call
increments showing calls taken, # coming in, % that go to voice mail, etc.
I
developed a report from a Total Query that groups on the time intervals
and
Averages some of different fields. The table name is All Calls (if you're
looking at the SQL below).

Although I used Design View to make it, here's the SQL for the query (if
you're interested or it will help). Sorry if it gets too messy with the
AS
pieces. I'm sure it came from the "renaming" I did to make the fields
more
intuitive as I worked on the report.

SELECT [All Calls].[Start Time], Avg([All Calls].[ACD Calls]) AS [ACD
Calls], Avg([All Calls].[Avg ACD Time]) AS [Avg ACD Time], Avg([All
Calls].[Max Delay]) AS [Max Delay], Avg([All Calls].[Avg Speed Ans]) AS
[Avg
Ans Speed], Avg([All Calls].[Avg Aban Time]) AS [Avg Aban Time], Avg([All
Calls].[Aban Calls]) AS [Aban Calls], Avg([% Aband Calls]/100) AS [%
Aban],
Avg([All Calls].[Flow Out]) AS [Voice Mail Calls], Avg([% Flow Out
Calls]/100) AS [% Voice Mail], Avg([All Calls].[Avg Pos Staff]) AS [Avg
Pos
Staff]
FROM [All Calls]
GROUP BY [All Calls].[Start Time];


PROBLEM: The report displays my fields (which are essentially averages)
just fine in the Details section. Now I would like to have an overall
Average displayed. I keep getting a #Error when the report is run.

The MS site I found
http://office.microsoft.com/en-us/assistance/HA011814481033.aspx
seems to indicate it's not recognizing which field to Average. I've tried
renaming fields on the report with underscores between words. For
example,
one of the fields is [ACD Calls]. Using the properties of the control, I
changed the name to ACD_Calls. Then in my header, I tried a control using
=Avg([ACD_Calls]) thinking with a different name, it could recognize it.
That still gives me #Error. By using the underscore in the name, I was
hoping I took out all possibilities of the circular reference as well (as
indicated in the article).

Any other suggestions? I would think with all Access can do it should be
capable of figuring an Average of Averages.

If you need some further/better explanation of what I'm trying to do or
would like to see the DB I have, let me know.
Thanks so much for any insight.
Marcia