CrossTab To Calculate Average

  • Thread starter Thread starter Troy1
  • Start date Start date
T

Troy1

I have a table that that stores number of donations
received by hour periods by date at Goodwill donation
stations. I have a crosstab query that sums number of
donations by weekday; i.e, Mon, Tue etc. My rows are the
station names, columns are weekday name and intersections
are Qty (sum). This sum is cumulative.

I need a query to calculate average number of donations
per weekday. When I try the same logic replacing "sum"
with"avg", it calculates the average per hour vice the
average per weekday.

I will appreciate any help
 
It seems that somewhere in your queries you have the date grouped on hour,
which would cause the calculation of average per hour. This grouping should
be on day. Instead of gouping on the original date field (which actually
includes date and time) calculate the day using a date function, and group
on this calculated "day" field. (I didn't test this).
 
I have both a medium date and a short time fields in the
table. I created a base query to generate a Day field
using: Format([DateField], "dddd"). That query include
the station and Qty fields from the table. Then I
created a crosstab query from the base query and it
produces the sum of donations by weekday.

When I use the same base query to create a crosstab
with "avg(Qty)" vice "sum(Qty)", I get average per hour
period.

It appears I need a query that counts the number of each
weekday in a range of dates as well as sums the donations
for each weekday in the range to be able to calculate the
average per weekday. I have not figured out how to do
that.
 
Instead of Avg(Qty) try Sum(Qty)/Count(Qty). I know this is what Avg()
should do but sometimes Crosstabs don't work the way you think.

Kelvin

Troy1 said:
I have both a medium date and a short time fields in the
table. I created a base query to generate a Day field
using: Format([DateField], "dddd"). That query include
the station and Qty fields from the table. Then I
created a crosstab query from the base query and it
produces the sum of donations by weekday.

When I use the same base query to create a crosstab
with "avg(Qty)" vice "sum(Qty)", I get average per hour
period.

It appears I need a query that counts the number of each
weekday in a range of dates as well as sums the donations
for each weekday in the range to be able to calculate the
average per weekday. I have not figured out how to do
that.

-----Original Message-----
It seems that somewhere in your queries you have the date grouped on hour,
which would cause the calculation of average per hour. This grouping should
be on day. Instead of gouping on the original date field (which actually
includes date and time) calculate the day using a date function, and group
on this calculated "day" field. (I didn't test this).





.
Click to expand...
Click to expand...
 
That seems plausible, but it is not an option in wizard
and I do not know how to do it otherwise.

Any suggestion?

Thanks
-----Original Message-----
Instead of Avg(Qty) try Sum(Qty)/Count(Qty). I know this is what Avg()
should do but sometimes Crosstabs don't work the way you think.

Kelvin

I have both a medium date and a short time fields in the
table. I created a base query to generate a Day field
using: Format([DateField], "dddd"). That query include
the station and Qty fields from the table. Then I
created a crosstab query from the base query and it
produces the sum of donations by weekday.

When I use the same base query to create a crosstab
with "avg(Qty)" vice "sum(Qty)", I get average per hour
period.

It appears I need a query that counts the number of each
weekday in a range of dates as well as sums the donations
for each weekday in the range to be able to calculate the
average per weekday. I have not figured out how to do
that.

-----Original Message-----
It seems that somewhere in your queries you have the date grouped on hour,
which would cause the calculation of average per hour. This grouping should
be on day. Instead of gouping on the original date field (which actually
includes date and time) calculate the day using a date function, and group
on this calculated "day" field. (I didn't test this).


I have a table that that stores number of donations
received by hour periods by date at Goodwill donation
stations. I have a crosstab query that sums number of
donations by weekday; i.e, Mon, Tue etc. My rows
Click to expand...
are
the
station names, columns are weekday name and intersections
are Qty (sum). This sum is cumulative.

I need a query to calculate average number of donations
per weekday. When I try the same logic replacing "sum"
with"avg", it calculates the average per hour vice the
average per weekday.

I will appreciate any help


.
Click to expand...
Click to expand...


.
Click to expand...
 
You will have to do this manually. After using the wizard to create the
crosstab go into design view. You should see a field where the "Total" row
says Sum or Avg depending on what you picked from the wizard. Change this
to "Expression" then modify the "Field" row to say
Sum([NameOfField]/Count([NameOfField]). Access should automaticallt add a
"Exp1:" if front of this. Run the query and see if this worked.

Kelvin

Troy1 said:
That seems plausible, but it is not an option in wizard
and I do not know how to do it otherwise.

Any suggestion?

Thanks
-----Original Message-----
Instead of Avg(Qty) try Sum(Qty)/Count(Qty). I know this is what Avg()
should do but sometimes Crosstabs don't work the way you think.

Kelvin

I have both a medium date and a short time fields in the
table. I created a base query to generate a Day field
using: Format([DateField], "dddd"). That query include
the station and Qty fields from the table. Then I
created a crosstab query from the base query and it
produces the sum of donations by weekday.

When I use the same base query to create a crosstab
with "avg(Qty)" vice "sum(Qty)", I get average per hour
period.

It appears I need a query that counts the number of each
weekday in a range of dates as well as sums the donations
for each weekday in the range to be able to calculate the
average per weekday. I have not figured out how to do
that.


-----Original Message-----
It seems that somewhere in your queries you have the
date grouped on hour,
which would cause the calculation of average per hour.
This grouping should
be on day. Instead of gouping on the original date
field (which actually
includes date and time) calculate the day using a date
function, and group
on this calculated "day" field. (I didn't test this).


message
I have a table that that stores number of donations
received by hour periods by date at Goodwill donation
stations. I have a crosstab query that sums number of
donations by weekday; i.e, Mon, Tue etc. My rows are
the
station names, columns are weekday name and
intersections
are Qty (sum). This sum is cumulative.

I need a query to calculate average number of donations
per weekday. When I try the same logic replacing "sum"
with"avg", it calculates the average per hour vice the
average per weekday.

I will appreciate any help


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top