Second POST.. Help with report summary

[Norma]

I am posting this again since I am on a deadline to get this
completed... Sorry for being redundant...

I have a table with a UPC#, CasesCompleted, TotalHours, standardGoal
(pieces per hour)
I am generating a report that groups by UPC and figures out production
goals. In the Detail section of each UPC, I have a calculated field
that looks like this:
=sum([CasesCompleted])/sum([totalHours])/([standardGoal])
This gives me the percentage of production. Each UPC has a different
standardGoal.
My question is: How can I get an average goal in the Report Footer by
using the amount in the calculated field when each UPC is using a
different standardGoal?
Any thought, ideas, code, or general direction would be greatly
appreciated.

The reason for the totaling of the goals for all UPCs is that
depending on the the percent of goal average that the production floor
has reached for the month, quarter or year,we have a program here that
pays out incentives for an average goal of over 105%. I hope that
clears it up a little. The individual UPC goals are a 'weighted' goal.
That is, it totals all the hours ran and all cases ran to determine
the goal%. The same will be for the Report Footer. Any ideas?
questions?

Is the report footer the best place to calculate this? If not, please
give me ideas on a query that may do the same.

[frasmus44]

Norma,
I had a similar problem for an actual waste vs std waste report I did.
When it comes to the group level, you need a weighted average of your
standard goal number. First, be aware that in reports you can
reference the names of other textboxes in your report just like you do
fields from the recordsource.

So if you create one textbox control in the group footer called
"GroupSumTotalHours" with a value =Sum([totalHours)

And another textbox control in the group footer called
"GroupWeightedStandardGoal" and give it a value
=Sum([standardGoal]*[totalHours])/[GroupSumTotalHours]

Then your formula for weighted efficiency would be:
=sum([CasesCompleted])/sum([totalHours])/[GroupWeightedStandardGoal]

Actually, it would be the same thing if you assign this value:
=sum([CasesCompleted])/[GroupSumTotalHours]/[GroupWeightedStandardGoal]

I hope this works for you.

[Norma]

Thanks for your help,
That works great for the group footer but how do I average the goal in
the report footer which includes all the UPCs hours and cases?
My report is generated by a date parameter that is entered. When I
tried to reference a text box that was created in the group footer, in
the report footer, I get the name of that text box popping up after I
enter the date parameters when trying to run the report.
Maybe I am trying to do something that is not possible in access.
If this is the case, can I export data from Access into Excel to do
these calculations and them import the Goal average back into access?

mh**********@spammotel.com (frasmus44) wrote in message news:<f8************************@posting.google.co m>...

Norma,
I had a similar problem for an actual waste vs std waste report I did.
When it comes to the group level, you need a weighted average of your
standard goal number. First, be aware that in reports you can
reference the names of other textboxes in your report just like you do
fields from the recordsource.

So if you create one textbox control in the group footer called
"GroupSumTotalHours" with a value =Sum([totalHours)

And another textbox control in the group footer called
"GroupWeightedStandardGoal" and give it a value
=Sum([standardGoal]*[totalHours])/[GroupSumTotalHours]

Then your formula for weighted efficiency would be:
=sum([CasesCompleted])/sum([totalHours])/[GroupWeightedStandardGoal]

Actually, it would be the same thing if you assign this value:
=sum([CasesCompleted])/[GroupSumTotalHours]/[GroupWeightedStandardGoal]

I hope this works for you.

[Norma]

Thanks for your help,
That works great for the group footer but how do I average the goal in
the report footer which includes all the UPCs hours and cases?
My report is generated by a date parameter that is entered. When I
tried to reference a text box that was created in the group footer, in
the report footer, I get the name of that text box popping up after I
enter the date parameters when trying to run the report.
Maybe I am trying to do something that is not possible in access.
If this is the case, can I export data from Access into Excel to do
these calculations and them import the Goal average back into access?

mh**********@spammotel.com (frasmus44) wrote in message news:<f8************************@posting.google.co m>...

Norma,
I had a similar problem for an actual waste vs std waste report I did.
When it comes to the group level, you need a weighted average of your
standard goal number. First, be aware that in reports you can
reference the names of other textboxes in your report just like you do
fields from the recordsource.

So if you create one textbox control in the group footer called
"GroupSumTotalHours" with a value =Sum([totalHours)

And another textbox control in the group footer called
"GroupWeightedStandardGoal" and give it a value
=Sum([standardGoal]*[totalHours])/[GroupSumTotalHours]

Then your formula for weighted efficiency would be:
=sum([CasesCompleted])/sum([totalHours])/[GroupWeightedStandardGoal]

Actually, it would be the same thing if you assign this value:
=sum([CasesCompleted])/[GroupSumTotalHours]/[GroupWeightedStandardGoal]

I hope this works for you.

[frasmus44]

nj**********@suscom.net (Norma) wrote in message news:<20**************************@posting.google. com>...

Thanks for your help,
That works great for the group footer but how do I average the goal in
the report footer which includes all the UPCs hours and cases?
My report is generated by a date parameter that is entered. When I
tried to reference a text box that was created in the group footer, in
the report footer, I get the name of that text box popping up after I
enter the date parameters when trying to run the report.
Maybe I am trying to do something that is not possible in access.
If this is the case, can I export data from Access into Excel to do
these calculations and them import the Goal average back into access?

The approach I mentioned before should work fine in the report footer
or in the group footer. My understanding is that Access takes the
functions you use like "=sum([CasesCompleted])" in the context of the
section where you use it.

So first create a textbox control in the REPORT footer called
"ReportSumTotalHours" with a value =Sum([totalHours)

Create another textbox control in the report footer called
"ReportWeightedStandardGoal" and give it a value
=Sum([standardGoal]*[totalHours])/[ReportSumTotalHours]

Then your formula for weighted efficiency over the whole report would
be:
=sum([CasesCompleted])/[ReportSumTotalHours]/[ReportWeightedStandardGoal]

Try that and let me know if it works for you!