Averaging sums

[jclark88]

I have a number of processes that run each day. Each process produces a
certain number of units. I am concerned with the base number of units
produced by each process every day, the aggregate number of units produced by
all processes every day, and the average aggregate number of units produced
every day by month.

(There's more than that, of course, but this is the crux of the problem.)

I have a report that is structured as:

Group By Date (by month), header1&footer1
Group By Date (total value) header2 & footer2
Sort By Process

My report roughly looks like this:

Header1: =[MONTH]
Header2: =[DATE]
Detail: =[Process Name] =[Units]
Footer2: =Sum([Units])
Footer1: =Avg([Units])

This correctly shows the daily sums, but gives me the average of the
individual processes, not the average sum. =Avg(Sum([Units])) isn't right,
but I'm not sure what is.

I'm sure this is trivial, and I'm missing something obvious, but any help is
appreciated.

 

[Marshall Barton]

I have a number of processes that run each day. Each process produces a
certain number of units. I am concerned with the base number of units
produced by each process every day, the aggregate number of units produced by
all processes every day, and the average aggregate number of units produced
every day by month.

(There's more than that, of course, but this is the crux of the problem.)

I have a report that is structured as:

Group By Date (by month), header1&footer1
Group By Date (total value) header2 & footer2
Sort By Process

My report roughly looks like this:

Header1: =[MONTH]
Header2: =[DATE]
Detail: =[Process Name] =[Units]
Footer2: =Sum([Units])
Footer1: =Avg([Units])

This correctly shows the daily sums, but gives me the average of the
individual processes, not the average sum. =Avg(Sum([Units])) isn't right,
but I'm not sure what is.

I'm sure this is trivial, and I'm missing something obvious, but any help is
appreciated.

Aggregating an aggregate is not trivial. Actually, it can
get pretty complicated.

In this case, I think you can avoid using another query with
a subreport by using two running sum text boxes in footer2.

txtRunCount with expression =1
txtRunSum with expression =Sum([Units])
both with their RunningSum property set to Over Group

Then the average text box in footer1 can use the expression:
=txtRunSum / txtRunCount

 

[jclark88]

I have a number of processes that run each day. Each process produces a
certain number of units. I am concerned with the base number of units
produced by each process every day, the aggregate number of units produced by
all processes every day, and the average aggregate number of units produced
every day by month.

(There's more than that, of course, but this is the crux of the problem.)

I have a report that is structured as:

Group By Date (by month), header1&footer1
Group By Date (total value) header2 & footer2
Sort By Process

My report roughly looks like this:

Header1: =[MONTH]
Header2: =[DATE]
Detail: =[Process Name] =[Units]
Footer2: =Sum([Units])
Footer1: =Avg([Units])

This correctly shows the daily sums, but gives me the average of the
individual processes, not the average sum. =Avg(Sum([Units])) isn't right,
but I'm not sure what is.

I'm sure this is trivial, and I'm missing something obvious, but any help is
appreciated.

Aggregating an aggregate is not trivial. Actually, it can
get pretty complicated.

In this case, I think you can avoid using another query with
a subreport by using two running sum text boxes in footer2.

txtRunCount with expression =1
txtRunSum with expression =Sum([Units])
both with their RunningSum property set to Over Group

Then the average text box in footer1 can use the expression:
=txtRunSum / txtRunCount

Thank you, that works very well. However, I also grab Max([Timeinseconds])
for each day. (These process run concurrently. The length of the longest
one tells me how long the overall batch took.) When using the same basic
structure - text box in the header to running sum Max(Timeinseconds) over the
group - that returns (number of days)x(largest time for all days in the
month), instead of the sum of the largest time for each day.

I worked around this by using a text box for Max(TimeInSeconds) with no
running sum, and another text box with a running sum and the first textbox as
its data source, and that gets me the correct value when I divide it by
txtRunCount.

This is Access 2007.

My question is: is this by design? I can see the "running sum" of maximums
being a number of things, but (#groups x largest item) isn't one that I can
rationalize.

 

[Marshall Barton]

I have a number of processes that run each day. Each process produces a
certain number of units. I am concerned with the base number of units
produced by each process every day, the aggregate number of units produced by
all processes every day, and the average aggregate number of units produced
every day by month.

(There's more than that, of course, but this is the crux of the problem.)

I have a report that is structured as:

Group By Date (by month), header1&footer1
Group By Date (total value) header2 & footer2
Sort By Process

My report roughly looks like this:

Header1: =[MONTH]
Header2: =[DATE]
Detail: =[Process Name] =[Units]
Footer2: =Sum([Units])
Footer1: =Avg([Units])

This correctly shows the daily sums, but gives me the average of the
individual processes, not the average sum. =Avg(Sum([Units])) isn't right,
but I'm not sure what is.

I'm sure this is trivial, and I'm missing something obvious, but any help is
appreciated.

Aggregating an aggregate is not trivial. Actually, it can
get pretty complicated.

In this case, I think you can avoid using another query with
a subreport by using two running sum text boxes in footer2.

txtRunCount with expression =1
txtRunSum with expression =Sum([Units])
both with their RunningSum property set to Over Group

Then the average text box in footer1 can use the expression:
=txtRunSum / txtRunCount

Thank you, that works very well. However, I also grab Max([Timeinseconds])
for each day. (These process run concurrently. The length of the longest
one tells me how long the overall batch took.) When using the same basic
structure - text box in the header to running sum Max(Timeinseconds) over the
group - that returns (number of days)x(largest time for all days in the
month), instead of the sum of the largest time for each day.

I worked around this by using a text box for Max(TimeInSeconds) with no
running sum, and another text box with a running sum and the first textbox as
its data source, and that gets me the correct value when I divide it by
txtRunCount.

This is Access 2007.

My question is: is this by design? I can see the "running sum" of maximums
being a number of things, but (#groups x largest item) isn't one that I can
rationalize.

I've never heard of such a thing. It sounds a little like
the kind of thing that might happen if the calculations were
done in a strange order such as referring to a running sum
text box before it has been calculated (e,g. in a group
header instead of the group footer).