The report's source is the Audit Details Query. The query looks at all
records within a defined timeframe. It outputs various info about each
audit
(Business Unit, Department within the Business Unit, Product Family,
Class,
Total Lot Qty, Total Sample Size, and Total Qty Defective) to name a few
of
those relevant to this discussion. It does not perform any calculations.
The report then groups first by Date (Monthly only), then by BU, then by
Class (which is either Acceptable or Reject). After the Class grouping
header, it displays all of the Detail data, notably the Qtys that I
mentioned. Next to these qty's, I created another text box (BU OQL) with
the
control set to "=([Defect Qty]/[Sample Qty])*([Lot Qty]/[Sum Of Lot
Qty1])*1000000". The [Sum of Lot Qty1] control is in the Business Unit
footer and provides me with the total Lot Qty of parts that were audited.
There is another text box (Plant OQL) with a similar calculation, only
this
time the Total Lot Qty is for all products audited plant-wide. These
calculations provide me with a calculation of the BU and Plant OQL
contribution for each audit.
I then have a few text boxes in the Class footer and BU footer to give me
the totals for each of the groups. And then at the end, in the Date
footer,
I sum it all up for a Plant Total.
As the report runs through each BU it does the calcs, sums them up in the
BU
footer and then moves on to the next BU. My problem is that this report
may
be up to 20 pages long, and I would like to display all of the individual
BU
sums on one page at the end of the report.
I don't know if this helped you to understand or worked to pull you closer
towards the state of confusion that I am currently in. Hope it helped!
Thanks AGAIN!!!
Duane Hookom said:
Your example shows a simple sum of the defects. Maybe you should tell us
more about your calculated information.
--
Duane Hookom
MS Access MVP
cainentm said:
Duane,
It appears to me that the problem with this is that the info that I am
trying to summarize at the end of the report is calculated information.
That
is to say that the information is calculated in the report, not the
underlying query. So as the report moves from group to group (Dept to
Dept)
the calculation can't be referred to later in the report. Thanks for
your
help with this. Please let me know if I need to more specifically
describe
what the report is doing - and what I WANT it to do!
Mike
:
Create a totals query similar to your report's record source. Use this
totals query as the record source for a subreport. Place the subreport
in
your main report's footer section.
--
Duane Hookom
MS Access MVP
--
in
message Will try to find a report I have at home that does this over the
weekend
sorry couldn't help but there is a way I assure you.
Dwight
--
Hansford D. Cornett
:
Hansford,
That was the 1st thing that I thought about but this doesn't work,
because
the report is grouped on "Department". Therefore, the Department
group
footer will calculate a sum for whatever Department is being
grouped
now,
but
when it goes to the next Department, the footer uses the same
calculation
for
this sum. You can't go into the Department footer and create a
field
for
Dept A, Dept B, etc, because its always just "Department". I know
what I
am
trying to say, but i am not sure if I am making sense?! Thanks for
the
help
though. Any other thoughts?
:
Yes what ever the name of the field is for the Dep Name and the
Dep
Sum
place
a field along side dept 1 and dept 2 name them DeptA and DeptB
and
then
set
visible to no. Then in the Report Footer add two fields naming
and
place =
[DeptA] and = [DeptB] in the source event and it should give you
the
result
you want and change as they are printed. If there are more than
2
departments you need to do this for each one.
--
Hansford D. Cornett
:
My report groups audit defect data by Department and displays a
calculated
sum in the Department Footer for the number of defects found in
that
Department. But this report may be many pages long and I would
like
to add a
summary to the end of the report that would re-cap each of the
Department
sums in one spot. For example, the report shows (simplified):
Dept 1
Audit Defects
1 3
2 1
3 4
______________________________
Dept Sum: 8
_______________________________________________
Dept 2
Audit Defects
1 1
2 0
3 3
______________________________
Dept Sum: 4
_______________________________________________
(This is what I want on the last page)
Summary:
Dept 1 8
Dept 2 4
______________________________
Plant Total: 12
Thanks for any help!
