using a footer total in details section

[jkendrick75]

i want my report to look like the following,

Between [StartDate] and [EndDate]
=PartNumber Header===============================
Part Number [PartNum]
=Details Section==================================
Defect Code Defect Quantitiy DefectPercentage
[defectcode] [defectquantity] [PercentDefQty]
=Part Number Footer===============================
Total sorted: [sumofTotalSort] Total Defects: [sumofDefects]
Percentage of Defects: [DefectPercentage]

the [PercentDefQty] text box is based on taking the [defectquantity] text
box in the Details Section and dividing that number by [sumofDefects] in the
Part Number Footer section. however as the footer section is not processed
until all of the details section is processed, how do i get the same number
to use in the Details Section as i will get in the [sumofDefects] text box?

just to clarify, there will be some part numbers that may only have 1
defect, so there will only be 1 defect code and 1 defectquantity listed.
however, there could be up to 28 defect codes for that 1 part also. what i
am trying to get is the percentage of each defect compared to the total of
defects.

any help with this would be greatly appreciated. thanks in advance.

 

[Marshall Barton]

i want my report to look like the following,

Between [StartDate] and [EndDate]
=PartNumber Header===============================
Part Number [PartNum]
=Details Section==================================
Defect Code Defect Quantitiy DefectPercentage
[defectcode] [defectquantity] [PercentDefQty]
=Part Number Footer===============================
Total sorted: [sumofTotalSort] Total Defects: [sumofDefects]
Percentage of Defects: [DefectPercentage]

the [PercentDefQty] text box is based on taking the [defectquantity] text
box in the Details Section and dividing that number by [sumofDefects] in the
Part Number Footer section. however as the footer section is not processed
until all of the details section is processed, how do i get the same number
to use in the Details Section as i will get in the [sumofDefects] text box?

just to clarify, there will be some part numbers that may only have 1
defect, so there will only be 1 defect code and 1 defectquantity listed.
however, there could be up to 28 defect codes for that 1 part also. what i
am trying to get is the percentage of each defect compared to the total of
defects.

Place a hidden text box with the sumofdefects value in the
group header section and use the in the details.

 

[jkendrick75]

i kinda thought that was what i would have to do, but how do i get that
number? doesn't the summing what defects will be in the details section take
place in the details section? such as

part# 123
defect code defect quantity
3 5
2 3
sum of defects 8

the defect quantity for defect code 3 could possibly be a total of five
different days, each having a defect code of 3 with a quantity of 1. so if i
put the hiddent text box with the sum in it, in the part number header
section, this would give me the sum just for this one part and no other part
numbers?

i want my report to look like the following,

Between [StartDate] and [EndDate]
=PartNumber Header===============================
Part Number [PartNum]
=Details Section==================================
Defect Code Defect Quantitiy DefectPercentage
[defectcode] [defectquantity] [PercentDefQty]
=Part Number Footer===============================
Total sorted: [sumofTotalSort] Total Defects: [sumofDefects]
Percentage of Defects: [DefectPercentage]

the [PercentDefQty] text box is based on taking the [defectquantity] text
box in the Details Section and dividing that number by [sumofDefects] in the
Part Number Footer section. however as the footer section is not processed
until all of the details section is processed, how do i get the same number
to use in the Details Section as i will get in the [sumofDefects] text box?

just to clarify, there will be some part numbers that may only have 1
defect, so there will only be 1 defect code and 1 defectquantity listed.
however, there could be up to 28 defect codes for that 1 part also. what i
am trying to get is the percentage of each defect compared to the total of
defects.

Place a hidden text box with the sumofdefects value in the
group header section and use the in the details.

 

[Marshall Barton]

i kinda thought that was what i would have to do, but how do i get that
number? doesn't the summing what defects will be in the details section take
place in the details section? such as

part# 123
defect code defect quantity
3 5
2 3
sum of defects 8

the defect quantity for defect code 3 could possibly be a total of five
different days, each having a defect code of 3 with a quantity of 1. so if i
put the hiddent text box with the sum in it, in the part number header
section, this would give me the sum just for this one part and no other part
numbers?

I was under the impression that you wanted the percentage of
total defects for each detail in each part. I now think you
want the percentage of each defect code in each part??

If I finally have it right, then you will need to group on
the defect code too. Easiest is to add another level to the
report's Sorting and Grouping and display the percentage in
the defect code header/footer.

However, you may prefer to use a subreport in the part
footer. The subreport's record source query would then be a
Totals type query that groups by both the part# and the
defect code with the Sum on the quantity. The Link
Master/Child settings would be the part#.
--
Marsh
MVP [MS Access]

i want my report to look like the following,

Between [StartDate] and [EndDate]
=PartNumber Header===============================
Part Number [PartNum]
=Details Section==================================
Defect Code Defect Quantitiy DefectPercentage
[defectcode] [defectquantity] [PercentDefQty]
=Part Number Footer===============================
Total sorted: [sumofTotalSort] Total Defects: [sumofDefects]
Percentage of Defects: [DefectPercentage]

the [PercentDefQty] text box is based on taking the [defectquantity] text
box in the Details Section and dividing that number by [sumofDefects] in the
Part Number Footer section. however as the footer section is not processed
until all of the details section is processed, how do i get the same number
to use in the Details Section as i will get in the [sumofDefects] text box?

just to clarify, there will be some part numbers that may only have 1
defect, so there will only be 1 defect code and 1 defectquantity listed.
however, there could be up to 28 defect codes for that 1 part also. what i
am trying to get is the percentage of each defect compared to the total of
defects.

Place a hidden text box with the sumofdefects value in the
group header section and use the in the details.

 

[jkendrick75]

that's correct, i want the percentage of defects for that defect code, for
that part group, within the date range parameters asked when the report
opens. i have tried to use a subreport in the part header, as this would be
processed before the details section, correct? not getting anywhere with
this, as depending upon what query i am running and what recordsource the
subreport sees, i am getting extra blank pages between part numbers. also
i'm not sure that adding another level to the reports sorting and grouping
and then displaying the percentage in the defect code header and footer would
work, as there is no [sumofdefects] number until the
details section has been done and sorted. in order to get the
[percentdefqty]number, it needs to have the same number that will show up in
the [sumofdefects] location, dividing the [defectquantity] by [sumofdefects]
and then multiplying by 100, will get the [percentdefqty] number for each
defect code. any other ideas or instructions on how to accomplish this would
be great, and thanks for looking into this.

i kinda thought that was what i would have to do, but how do i get that
number? doesn't the summing what defects will be in the details section take
place in the details section? such as

part# 123
defect code defect quantity
3 5
2 3
sum of defects 8

the defect quantity for defect code 3 could possibly be a total of five
different days, each having a defect code of 3 with a quantity of 1. so if i
put the hiddent text box with the sum in it, in the part number header
section, this would give me the sum just for this one part and no other part
numbers?

I was under the impression that you wanted the percentage of
total defects for each detail in each part. I now think you
want the percentage of each defect code in each part??

If I finally have it right, then you will need to group on
the defect code too. Easiest is to add another level to the
report's Sorting and Grouping and display the percentage in
the defect code header/footer.

However, you may prefer to use a subreport in the part
footer. The subreport's record source query would then be a
Totals type query that groups by both the part# and the
defect code with the Sum on the quantity. The Link
Master/Child settings would be the part#.
--
Marsh
MVP [MS Access]

jkendrick75 wrote:
i want my report to look like the following,

Between [StartDate] and [EndDate]
=PartNumber Header===============================
Part Number [PartNum]
=Details Section==================================
Defect Code Defect Quantitiy DefectPercentage
[defectcode] [defectquantity] [PercentDefQty]
=Part Number Footer===============================
Total sorted: [sumofTotalSort] Total Defects: [sumofDefects]
Percentage of Defects: [DefectPercentage]

the [PercentDefQty] text box is based on taking the [defectquantity] text
box in the Details Section and dividing that number by [sumofDefects] in the
Part Number Footer section. however as the footer section is not processed
until all of the details section is processed, how do i get the same number
to use in the Details Section as i will get in the [sumofDefects] text box?

just to clarify, there will be some part numbers that may only have 1
defect, so there will only be 1 defect code and 1 defectquantity listed.
however, there could be up to 28 defect codes for that 1 part also. what i
am trying to get is the percentage of each defect compared to the total of
defects.

Place a hidden text box with the sumofdefects value in the
group header section and use the in the details.

 

[Marshall Barton]

Comments/questions inline
--
Marsh
MVP [MS Access]

that's correct, i want the percentage of defects for that defect code, for
that part group, within the date range parameters asked when the report
opens.

So, if the part has a total of 10 defects and a specific
defect code for that part has two details with 2 and 3
defects, you want the part header section to display that
defect and a percentage of 50. Right?

i have tried to use a subreport in the part header, as this would be
processed before the details section, correct?

Correct, but the subreport would have calculated the total
for each defect code for the part. The part group header
calculates the total defects for the part with a simple text
box expression of =Sum([defect quantity]) so the subreport
can display the percentage by using an expression like:
=100 * totaldefectcode / Parent.txtTotalDefects

not getting anywhere with
this, as depending upon what query i am running and what recordsource the
subreport sees, i am getting extra blank pages between part numbers.

Blank pages are a separate issue and are easily dealt with
using section properties. Don't let this clutter your
thinking.

also
i'm not sure that adding another level to the reports sorting and grouping
and then displaying the percentage in the defect code header and footer would
work, as there is no [sumofdefects] number until the
details section has been done and sorted.

Not true, As I said above, the total of all defects,
sumofdefects, for the part can be calculated in the part
header.

in order to get the
[percentdefqty]number, it needs to have the same number that will show up in
the [sumofdefects] location, dividing the [defectquantity] by [sumofdefects]
and then multiplying by 100, will get the [percentdefqty] number for each
defect code. any other ideas or instructions on how to accomplish this would
be great, and thanks for looking into this.

No, I have no other ideas. Unless I have completely missed
the point, the two approaches I've suggested should both be
able to do this.

I was under the impression that you wanted the percentage of
total defects for each detail in each part. I now think you
want the percentage of each defect code in each part??

If I finally have it right, then you will need to group on
the defect code too. Easiest is to add another level to the
report's Sorting and Grouping and display the percentage in
the defect code header/footer.

However, you may prefer to use a subreport in the part
footer. The subreport's record source query would then be a
Totals type query that groups by both the part# and the
defect code with the Sum on the quantity. The Link
Master/Child settings would be the part#.

jkendrick75 wrote:
i want my report to look like the following,

Between [StartDate] and [EndDate]
=PartNumber Header===============================
Part Number [PartNum]
=Details Section==================================
Defect Code Defect Quantitiy DefectPercentage
[defectcode] [defectquantity] [PercentDefQty]
=Part Number Footer===============================
Total sorted: [sumofTotalSort] Total Defects: [sumofDefects]
Percentage of Defects: [DefectPercentage]

the [PercentDefQty] text box is based on taking the [defectquantity] text
box in the Details Section and dividing that number by [sumofDefects] in the
Part Number Footer section. however as the footer section is not processed
until all of the details section is processed, how do i get the same number
to use in the Details Section as i will get in the [sumofDefects] text box?

just to clarify, there will be some part numbers that may only have 1
defect, so there will only be 1 defect code and 1 defectquantity listed.
however, there could be up to 28 defect codes for that 1 part also. what i
am trying to get is the percentage of each defect compared to the total of
defects.


:
Place a hidden text box with the sumofdefects value in the
group header section and use the in the details.

 

[jkendrick75]

for this part you are close....

So, if the part has a total of 10 defects and a specific
defect code for that part has two details with 2 and 3
defects, you want the part header section to display that
defect and a percentage of 50. Right?

for the items i gave in my previous post, plus a little bit more...
part# 123
defect code defect quantity {Defect Percentage}
3 5 {62.5}
2 3 {37.5}
sum of defects 8

i put { } around the new stuff, which is what i am trying to get to display
in the details section. the defect percentage is for the number of defects
for the code (code 3 has quantity of 5), divided by the total defects for
that part (total defects for part is 8) so it would be '(5/8)*100 =
[percentdefqty]'.

just to clarify, this report is going to be used over a date range, say a
week or a month, so all the different defects for a part are going to be
added together (sorted by their defect codes (as shown above). the
[percentdefqty] is to show what the main defects for a part is, over a date
range.

one more thing, just to give a better idea of what the report actually looks
like
=Report Header===============================
Part Report
=Page Header================================
=MoldNumber Header===========================
Mold Number [txtMoldNumber]
=PartNumber Header===========================
Part Number [txtPartNumber]
Defect Codes DefectQuantity/Code Percentage of Defects
=Detail====================================
[Defect_Code1] [sumOfDef1_Qty] [percentdefqty]
=PartNumber Footer===========================
Total Sorted: [SumOfTotalPartsRan]
Total Defects/Part: [=Sum([sumofdefect1_quantity])
Defect Percentage:
[=((Sum([sumofdefect1_quantity])/[SumOfTotalPartsRan])*100)]
=Page Footer ===============================
[=Now()] [="Page " & [Page] & " of " [Pages]]
=Report Footer==============================

i put the entire layout of what i want in the report, i don't know if this
will help you to better understand what i am trying to accomplish. i have
gotten everything to work except for getting the [percentdefqty] in the
detail section. i will continue to try what you said in your previous post,
however today is my last day of work for about 10 days... so i will try to
pick up on these posts in 10 days. thank you for looking into this and have
a great holidy season...

Comments/questions inline
--
Marsh
MVP [MS Access]

that's correct, i want the percentage of defects for that defect code, for
that part group, within the date range parameters asked when the report
opens.

So, if the part has a total of 10 defects and a specific
defect code for that part has two details with 2 and 3
defects, you want the part header section to display that
defect and a percentage of 50. Right?

i have tried to use a subreport in the part header, as this would be
processed before the details section, correct?

Correct, but the subreport would have calculated the total
for each defect code for the part. The part group header
calculates the total defects for the part with a simple text
box expression of =Sum([defect quantity]) so the subreport
can display the percentage by using an expression like:
=100 * totaldefectcode / Parent.txtTotalDefects

not getting anywhere with
this, as depending upon what query i am running and what recordsource the
subreport sees, i am getting extra blank pages between part numbers.

Blank pages are a separate issue and are easily dealt with
using section properties. Don't let this clutter your
thinking.

also
i'm not sure that adding another level to the reports sorting and grouping
and then displaying the percentage in the defect code header and footer would
work, as there is no [sumofdefects] number until the
details section has been done and sorted.

Not true, As I said above, the total of all defects,
sumofdefects, for the part can be calculated in the part
header.

in order to get the
[percentdefqty]number, it needs to have the same number that will show up in
the [sumofdefects] location, dividing the [defectquantity] by [sumofdefects]
and then multiplying by 100, will get the [percentdefqty] number for each
defect code. any other ideas or instructions on how to accomplish this would
be great, and thanks for looking into this.

No, I have no other ideas. Unless I have completely missed
the point, the two approaches I've suggested should both be
able to do this.

jkendrick75 wrote:
i kinda thought that was what i would have to do, but how do i get that
number? doesn't the summing what defects will be in the details section take
place in the details section? such as

part# 123
defect code defect quantity
3 5
2 3
sum of defects 8

the defect quantity for defect code 3 could possibly be a total of five
different days, each having a defect code of 3 with a quantity of 1. so if i
put the hiddent text box with the sum in it, in the part number header
section, this would give me the sum just for this one part and no other part
numbers?

I was under the impression that you wanted the percentage of
total defects for each detail in each part. I now think you
want the percentage of each defect code in each part??

If I finally have it right, then you will need to group on
the defect code too. Easiest is to add another level to the
report's Sorting and Grouping and display the percentage in
the defect code header/footer.

However, you may prefer to use a subreport in the part
footer. The subreport's record source query would then be a
Totals type query that groups by both the part# and the
defect code with the Sum on the quantity. The Link
Master/Child settings would be the part#.


jkendrick75 wrote:
i want my report to look like the following,

Between [StartDate] and [EndDate]
=PartNumber Header===============================
Part Number [PartNum]
=Details Section==================================
Defect Code Defect Quantitiy DefectPercentage
[defectcode] [defectquantity] [PercentDefQty]
=Part Number Footer===============================
Total sorted: [sumofTotalSort] Total Defects: [sumofDefects]
Percentage of Defects: [DefectPercentage]

the [PercentDefQty] text box is based on taking the [defectquantity] text
box in the Details Section and dividing that number by [sumofDefects] in the
Part Number Footer section. however as the footer section is not processed
until all of the details section is processed, how do i get the same number
to use in the Details Section as i will get in the [sumofDefects] text box?

just to clarify, there will be some part numbers that may only have 1
defect, so there will only be 1 defect code and 1 defectquantity listed.
however, there could be up to 28 defect codes for that 1 part also. what i
am trying to get is the percentage of each defect compared to the total of
defects.


:
Place a hidden text box with the sumofdefects value in the
group header section and use the in the details.

 

[jkendrick75]

i was able to get the results that i wanted, using your idea of the text box
in the part number header section as "=Sum([sumofdefect1_quantity])". now,
another question is, i have added another grouping and sorting level to the
report. i am wanting to group by a shift number. i have already made the
change to the query to allow this, but i want to get a total parts sorted and
a total of defects for all parts grouped by a shift.

this is the report design now.

=Report Header===============================
Part Report Between [txtStartDate] & [txtEndDate]
=Page Header================================
=Shift Header================================
Shift: [txtShift]
=MoldNumber Header===========================
Mold Number [txtMoldNumber]
=PartNumber Header===========================
Part Number [txtPartNumber](hidden: [=Sum([sumofDefect1_quantity])
Defect Codes DefectQuantity/Code Percentage of Defects
=Detail====================================
[Defect_Code1] [sumOfDef1_Qty] [percentdefqty]
=PartNumber Footer===========================
Total Sorted: [SumOfTotalPartsRan]
Total Defects/Part: [=Sum([sumofdefect1_quantity])
Defect Percentage:
[=((Sum([sumofdefect1_quantity])/[SumOfTotalPartsRan])*100)]
=Shift Footer================================
Total Sorted for shift [Sum([txtPartSum])]
Total Defects for shift [Sum([txtDefectsSum])]
=Page Footer ===============================
[=Now()] [="Page " & [Page] & " of " [Pages]]
=Report Footer==============================

now what i am trying to do is get the [sum([txtPartSum])] and the
[sum9[txtDefectsSum])] to be the total of parts sorted and total of defects
sorted for the shift. i am trying to use the values from the text boxes in
the part footer section of each part number. but the number that i am
getting in these values for the shift footer section are totals of all three
shifts, i think. i have not been able to confirm this yet. i am going to
continue working on this, but if you have any ideas i would like to hear
them... thanks.

i want my report to look like the following,

Between [StartDate] and [EndDate]
=PartNumber Header===============================
Part Number [PartNum]
=Details Section==================================
Defect Code Defect Quantitiy DefectPercentage
[defectcode] [defectquantity] [PercentDefQty]
=Part Number Footer===============================
Total sorted: [sumofTotalSort] Total Defects: [sumofDefects]
Percentage of Defects: [DefectPercentage]

the [PercentDefQty] text box is based on taking the [defectquantity] text
box in the Details Section and dividing that number by [sumofDefects] in the
Part Number Footer section. however as the footer section is not processed
until all of the details section is processed, how do i get the same number
to use in the Details Section as i will get in the [sumofDefects] text box?

just to clarify, there will be some part numbers that may only have 1
defect, so there will only be 1 defect code and 1 defectquantity listed.
however, there could be up to 28 defect codes for that 1 part also. what i
am trying to get is the percentage of each defect compared to the total of
defects.

any help with this would be greatly appreciated. thanks in advance.

 

[Marshall Barton]

i was able to get the results that i wanted, using your idea of the text box
in the part number header section as "=Sum([sumofdefect1_quantity])". now,
another question is, i have added another grouping and sorting level to the
report. i am wanting to group by a shift number. i have already made the
change to the query to allow this, but i want to get a total parts sorted and
a total of defects for all parts grouped by a shift.

this is the report design now.

=Report Header===============================
Part Report Between [txtStartDate] & [txtEndDate]
=Page Header================================
=Shift Header================================
Shift: [txtShift]
=MoldNumber Header===========================
Mold Number [txtMoldNumber]
=PartNumber Header===========================
Part Number [txtPartNumber](hidden: [=Sum([sumofDefect1_quantity])
Defect Codes DefectQuantity/Code Percentage of Defects
=Detail====================================
[Defect_Code1] [sumOfDef1_Qty] [percentdefqty]
=PartNumber Footer===========================
Total Sorted: [SumOfTotalPartsRan]
Total Defects/Part: [=Sum([sumofdefect1_quantity])
Defect Percentage:
[=((Sum([sumofdefect1_quantity])/[SumOfTotalPartsRan])*100)]
=Shift Footer================================
Total Sorted for shift [Sum([txtPartSum])]
Total Defects for shift [Sum([txtDefectsSum])]
=Page Footer ===============================
[=Now()] [="Page " & [Page] & " of " [Pages]]
=Report Footer==============================

now what i am trying to do is get the [sum([txtPartSum])] and the
[sum9[txtDefectsSum])] to be the total of parts sorted and total of defects
sorted for the shift. i am trying to use the values from the text boxes in
the part footer section of each part number. but the number that i am
getting in these values for the shift footer section are totals of all three
shifts, i think. i have not been able to confirm this yet.

You can use the Sum function in both the group header and
footer of each level of grouping. It will total the
specified **field** (not control) for that group level. You
do not need to try to total the values in your text box
controls (which Sum is unaware of and can not operate on).

 

[jkendrick75]

hey back again,
trying to do as you said in your last post, but it is giving me a number of
21,864 when it should give me a number of 5,466. i put the control source
for the text box in the shift footer as "=Sum([SumOfTotalPartsRan])" where
the [SumOfTotalPartsRan] is the text box in the part number footer section.
in this instance, there are 5 part numbers for 1 shift within my date range.

i thought that it was trying to sum all of the parts sorted for each shift
within the date range, but when adding that number up from what is printed in
the report, the number is 21781. (83 less than the 21,864 that i am getting)

what is it that i am missing?

i was able to get the results that i wanted, using your idea of the text box
in the part number header section as "=Sum([sumofdefect1_quantity])". now,
another question is, i have added another grouping and sorting level to the
report. i am wanting to group by a shift number. i have already made the
change to the query to allow this, but i want to get a total parts sorted and
a total of defects for all parts grouped by a shift.

this is the report design now.

=Report Header===============================
Part Report Between [txtStartDate] & [txtEndDate]
=Page Header================================
=Shift Header================================
Shift: [txtShift]
=MoldNumber Header===========================
Mold Number [txtMoldNumber]
=PartNumber Header===========================
Part Number [txtPartNumber](hidden: [=Sum([sumofDefect1_quantity])
Defect Codes DefectQuantity/Code Percentage of Defects
=Detail====================================
[Defect_Code1] [sumOfDef1_Qty] [percentdefqty]
=PartNumber Footer===========================
Total Sorted: [SumOfTotalPartsRan]
Total Defects/Part: [=Sum([sumofdefect1_quantity])
Defect Percentage:
[=((Sum([sumofdefect1_quantity])/[SumOfTotalPartsRan])*100)]
=Shift Footer================================
Total Sorted for shift [Sum([txtPartSum])]
Total Defects for shift [Sum([txtDefectsSum])]
=Page Footer ===============================
[=Now()] [="Page " & [Page] & " of " [Pages]]
=Report Footer==============================

now what i am trying to do is get the [sum([txtPartSum])] and the
[sum9[txtDefectsSum])] to be the total of parts sorted and total of defects
sorted for the shift. i am trying to use the values from the text boxes in
the part footer section of each part number. but the number that i am
getting in these values for the shift footer section are totals of all three
shifts, i think. i have not been able to confirm this yet.

You can use the Sum function in both the group header and
footer of each level of grouping. It will total the
specified **field** (not control) for that group level. You
do not need to try to total the values in your text box
controls (which Sum is unaware of and can not operate on).

 

[Marshall Barton]

trying to do as you said in your last post, but it is giving me a number of
21,864 when it should give me a number of 5,466. i put the control source
for the text box in the shift footer as "=Sum([SumOfTotalPartsRan])" where
the [SumOfTotalPartsRan] is the text box in the part number footer section.
in this instance, there are 5 part numbers for 1 shift within my date range.

i thought that it was trying to sum all of the parts sorted for each shift
within the date range, but when adding that number up from what is printed in
the report, the number is 21781. (83 less than the 21,864 that i am getting)

Boy, am I confused now. First you said that using
=Sum([SumOfTotalPartsRan]) in the Part footer worked the way
you want, but now you're telling me that the exact same text
box expression in the shift footer is giving you 4 times the
correct number. I just don't see how that's possible,
either they're both correct or they're both wrong??

Take a look at the report's record source table/query, is
the SumOfTotalPartsRan field's value duplicated on the
records with the same part number??

Remember what I said before, the aggregate functions are
unaware of controls on the report. So when you say that
"[SumOfTotalPartsRan] is the text box in the part number
footer", you are incorrect. Even if you have a text box
with that name, Sum is still goiing to sum the **field** in
the report's record source table/query.

I suggest that you do more investigation to see if you can
figure out what is actually being summed and if you still
need more help, try post back with more detailed info s I
can get a grip on what you're doing.
--
Marsh
MVP [MS Access]

i was able to get the results that i wanted, using your idea of the text box
in the part number header section as "=Sum([sumofdefect1_quantity])". now,
another question is, i have added another grouping and sorting level to the
report. i am wanting to group by a shift number. i have already made the
change to the query to allow this, but i want to get a total parts sorted and
a total of defects for all parts grouped by a shift.

this is the report design now.

=Report Header===============================
Part Report Between [txtStartDate] & [txtEndDate]
=Page Header================================
=Shift Header================================
Shift: [txtShift]
=MoldNumber Header===========================
Mold Number [txtMoldNumber]
=PartNumber Header===========================
Part Number [txtPartNumber](hidden: [=Sum([sumofDefect1_quantity])
Defect Codes DefectQuantity/Code Percentage of Defects
=Detail====================================
[Defect_Code1] [sumOfDef1_Qty] [percentdefqty]
=PartNumber Footer===========================
Total Sorted: [SumOfTotalPartsRan]
Total Defects/Part: [=Sum([sumofdefect1_quantity])
Defect Percentage:
[=((Sum([sumofdefect1_quantity])/[SumOfTotalPartsRan])*100)]
=Shift Footer================================
Total Sorted for shift [Sum([txtPartSum])]
Total Defects for shift [Sum([txtDefectsSum])]
=Page Footer ===============================
[=Now()] [="Page " & [Page] & " of " [Pages]]
=Report Footer==============================

now what i am trying to do is get the [sum([txtPartSum])] and the
[sum9[txtDefectsSum])] to be the total of parts sorted and total of defects
sorted for the shift. i am trying to use the values from the text boxes in
the part footer section of each part number. but the number that i am
getting in these values for the shift footer section are totals of all three
shifts, i think. i have not been able to confirm this yet.

You can use the Sum function in both the group header and
footer of each level of grouping. It will total the
specified **field** (not control) for that group level. You
do not need to try to total the values in your text box
controls (which Sum is unaware of and can not operate on).

 

[jkendrick75]

Remember what I said before, the aggregate functions are

unaware of controls on the report. So when you say that
"[SumOfTotalPartsRan] is the text box in the part number
footer", you are incorrect. Even if you have a text box
with that name, Sum is still goiing to sum the **field** in
the report's record source table/query

just to clarify, for this i understand that the summing won't work using the
value of a text control, when i do the [SumOfTotalPartsRan] that is the
source field that the box is seeing.

i have looked into the query and i'm not sure if there is anything wrong
with it. i have totaled the values up by hand from the database and i am
getting the correct numbers in the report for the part footer section. for
the shift section however, i have noticed that for each defect code, the
totalpartsran is being added to itself for that part. (e.g. part 123 has 3
different defect codes, and a totalpartsran of 100. the number that is being
passed to the sum(sumoftotalpartsran) is 300, 100 for each defect code)
i am including the SQL for the query that is the recordsource of the report
below

SELECT qryDefectsSorted.PartNumber, qryDefectsSorted.Defect_Code1,
Sum(qryDefectsSorted.Defect1_Quantity) AS SumOfDefect1_Quantity,
Sum(qryDefectsSorted.TotalPartsRan) AS SumOfTotalPartsRan,
qryDefectsSorted.MoldNumber, qryDefectsSorted.Shift
FROM qryDefectsSorted
WHERE (((qryDefectsSorted.Date) Between
[forms]![frmsorteddefects]![startdate] And
[forms]![frmsorteddefects]![enddate]))
GROUP BY qryDefectsSorted.PartNumber, qryDefectsSorted.Defect_Code1,
qryDefectsSorted.MoldNumber, qryDefectsSorted.Shift;

i noticed in the GROUP BY section of the sql statement that it groups by
shift last, does this matter?

trying to do as you said in your last post, but it is giving me a number of
21,864 when it should give me a number of 5,466. i put the control source
for the text box in the shift footer as "=Sum([SumOfTotalPartsRan])" where
the [SumOfTotalPartsRan] is the text box in the part number footer section.
in this instance, there are 5 part numbers for 1 shift within my date range.

i thought that it was trying to sum all of the parts sorted for each shift
within the date range, but when adding that number up from what is printed in
the report, the number is 21781. (83 less than the 21,864 that i am getting)

Boy, am I confused now. First you said that using
=Sum([SumOfTotalPartsRan]) in the Part footer worked the way
you want, but now you're telling me that the exact same text
box expression in the shift footer is giving you 4 times the
correct number. I just don't see how that's possible,
either they're both correct or they're both wrong??

Take a look at the report's record source table/query, is
the SumOfTotalPartsRan field's value duplicated on the
records with the same part number??

Remember what I said before, the aggregate functions are
unaware of controls on the report. So when you say that
"[SumOfTotalPartsRan] is the text box in the part number
footer", you are incorrect. Even if you have a text box
with that name, Sum is still goiing to sum the **field** in
the report's record source table/query.

I suggest that you do more investigation to see if you can
figure out what is actually being summed and if you still
need more help, try post back with more detailed info s I
can get a grip on what you're doing.
--
Marsh
MVP [MS Access]

jkendrick75 wrote:
i was able to get the results that i wanted, using your idea of the text box
in the part number header section as "=Sum([sumofdefect1_quantity])". now,
another question is, i have added another grouping and sorting level to the
report. i am wanting to group by a shift number. i have already made the
change to the query to allow this, but i want to get a total parts sorted and
a total of defects for all parts grouped by a shift.

this is the report design now.

=Report Header===============================
Part Report Between [txtStartDate] & [txtEndDate]
=Page Header================================
=Shift Header================================
Shift: [txtShift]
=MoldNumber Header===========================
Mold Number [txtMoldNumber]
=PartNumber Header===========================
Part Number [txtPartNumber](hidden: [=Sum([sumofDefect1_quantity])
Defect Codes DefectQuantity/Code Percentage of Defects
=Detail====================================
[Defect_Code1] [sumOfDef1_Qty] [percentdefqty]
=PartNumber Footer===========================
Total Sorted: [SumOfTotalPartsRan]
Total Defects/Part: [=Sum([sumofdefect1_quantity])
Defect Percentage:
[=((Sum([sumofdefect1_quantity])/[SumOfTotalPartsRan])*100)]
=Shift Footer================================
Total Sorted for shift [Sum([txtPartSum])]
Total Defects for shift [Sum([txtDefectsSum])]
=Page Footer ===============================
[=Now()] [="Page " & [Page] & " of " [Pages]]
=Report Footer==============================

now what i am trying to do is get the [sum([txtPartSum])] and the
[sum9[txtDefectsSum])] to be the total of parts sorted and total of defects
sorted for the shift. i am trying to use the values from the text boxes in
the part footer section of each part number. but the number that i am
getting in these values for the shift footer section are totals of all three
shifts, i think. i have not been able to confirm this yet.

You can use the Sum function in both the group header and
footer of each level of grouping. It will total the
specified **field** (not control) for that group level. You
do not need to try to total the values in your text box
controls (which Sum is unaware of and can not operate on).

 

[Marshall Barton]

Remember what I said before, the aggregate functions are
unaware of controls on the report. So when you say that
"[SumOfTotalPartsRan] is the text box in the part number
footer", you are incorrect. Even if you have a text box
with that name, Sum is still goiing to sum the **field** in
the report's record source table/query

just to clarify, for this i understand that the summing won't work using the
value of a text control, when i do the [SumOfTotalPartsRan] that is the
source field that the box is seeing.

i have looked into the query and i'm not sure if there is anything wrong
with it. i have totaled the values up by hand from the database and i am
getting the correct numbers in the report for the part footer section. for
the shift section however, i have noticed that for each defect code, the
totalpartsran is being added to itself for that part. (e.g. part 123 has 3
different defect codes, and a totalpartsran of 100. the number that is being
passed to the sum(sumoftotalpartsran) is 300, 100 for each defect code)
i am including the SQL for the query that is the recordsource of the report
below

SELECT qryDefectsSorted.PartNumber, qryDefectsSorted.Defect_Code1,
Sum(qryDefectsSorted.Defect1_Quantity) AS SumOfDefect1_Quantity,
Sum(qryDefectsSorted.TotalPartsRan) AS SumOfTotalPartsRan,
qryDefectsSorted.MoldNumber, qryDefectsSorted.Shift
FROM qryDefectsSorted
WHERE (((qryDefectsSorted.Date) Between
[forms]![frmsorteddefects]![startdate] And
[forms]![frmsorteddefects]![enddate]))
GROUP BY qryDefectsSorted.PartNumber, qryDefectsSorted.Defect_Code1,
qryDefectsSorted.MoldNumber, qryDefectsSorted.Shift;

i noticed in the GROUP BY section of the sql statement that it groups by
shift last, does this matter?

Yes, it does matter that the defects are already sumed
before the report even sees the data. However, you can not
just remove Shift from the Group By clause, because every
field in a Group By type query must be either used in an
aggregate function or used in the Group By clause. I am too
confused by what you're doing here to be sure, but you
**might** be able to get what you want by removing Shift
from the Group By clause and using First(Shift) in the field
list.

On the other hand, what's confusing me is why you are trying
to sum the defects and parts ran in the query and again in
the report. I would think that one or the other, but not
both would be more appropriate.

 

[jkendrick75]

going by what i showed in a previous post for what i want the report to look
like, i am needing to show how many different defect codes for a particular
part number, what the totals were for each defect code (part 123 has a total
of 15 defects over a week period, with defect code 1 having 3 defects, code 3
having 5 defects, code 7 = 5 defects, and code 10 = 2 defects for this 1 week
period.) then i want a total of all parts ran for the same 1 week period
along with a total of all defects for the 1 week period (this period is just
an example, we will actually be using a form to enter a start and end date,
which is working fine...)

after further trial and error scenarios, i tried to use a text box in the
part number header that had a control source of
"=sum(sumoftotalpartsran)/count(defect_code1)" and this works as long as the
part number has the same number of parts ran during the period that is being
asked for. but what i was getting for a couple of shifts is part 123 had a
total parts ran of 2896 one time and 720 a second time. with 5 different
defect codes between them i was getting decimal number for an answer using
the "=sum(sumoftotalpartsran)/count(defect_code1)" setup.

i am thinking that i will do what i did with a previous database report
problem. i had setup a website where the database could be downloaded along
with snapshots of what the reports are looking like. (did this for a
different database)
don't know if that will help you get a better understanding of what i am
trying to do or not. will post back later today, when the site is up...

Remember what I said before, the aggregate functions are
unaware of controls on the report. So when you say that
"[SumOfTotalPartsRan] is the text box in the part number
footer", you are incorrect. Even if you have a text box
with that name, Sum is still goiing to sum the **field** in
the report's record source table/query

just to clarify, for this i understand that the summing won't work using the
value of a text control, when i do the [SumOfTotalPartsRan] that is the
source field that the box is seeing.

i have looked into the query and i'm not sure if there is anything wrong
with it. i have totaled the values up by hand from the database and i am
getting the correct numbers in the report for the part footer section. for
the shift section however, i have noticed that for each defect code, the
totalpartsran is being added to itself for that part. (e.g. part 123 has 3
different defect codes, and a totalpartsran of 100. the number that is being
passed to the sum(sumoftotalpartsran) is 300, 100 for each defect code)
i am including the SQL for the query that is the recordsource of the report
below

SELECT qryDefectsSorted.PartNumber, qryDefectsSorted.Defect_Code1,
Sum(qryDefectsSorted.Defect1_Quantity) AS SumOfDefect1_Quantity,
Sum(qryDefectsSorted.TotalPartsRan) AS SumOfTotalPartsRan,
qryDefectsSorted.MoldNumber, qryDefectsSorted.Shift
FROM qryDefectsSorted
WHERE (((qryDefectsSorted.Date) Between
[forms]![frmsorteddefects]![startdate] And
[forms]![frmsorteddefects]![enddate]))
GROUP BY qryDefectsSorted.PartNumber, qryDefectsSorted.Defect_Code1,
qryDefectsSorted.MoldNumber, qryDefectsSorted.Shift;

i noticed in the GROUP BY section of the sql statement that it groups by
shift last, does this matter?

Yes, it does matter that the defects are already sumed
before the report even sees the data. However, you can not
just remove Shift from the Group By clause, because every
field in a Group By type query must be either used in an
aggregate function or used in the Group By clause. I am too
confused by what you're doing here to be sure, but you
**might** be able to get what you want by removing Shift
from the Group By clause and using First(Shift) in the field
list.

On the other hand, what's confusing me is why you are trying
to sum the defects and parts ran in the query and again in
the report. I would think that one or the other, but not
both would be more appropriate.

 

[Marshall Barton]

going by what i showed in a previous post for what i want the report to look
like, i am needing to show how many different defect codes for a particular
part number, what the totals were for each defect code (part 123 has a total
of 15 defects over a week period, with defect code 1 having 3 defects, code 3
having 5 defects, code 7 = 5 defects, and code 10 = 2 defects for this 1 week
period.) then i want a total of all parts ran for the same 1 week period
along with a total of all defects for the 1 week period (this period is just
an example, we will actually be using a form to enter a start and end date,
which is working fine...)

after further trial and error scenarios, i tried to use a text box in the
part number header that had a control source of
"=sum(sumoftotalpartsran)/count(defect_code1)" and this works as long as the
part number has the same number of parts ran during the period that is being
asked for. but what i was getting for a couple of shifts is part 123 had a
total parts ran of 2896 one time and 720 a second time. with 5 different
defect codes between them i was getting decimal number for an answer using
the "=sum(sumoftotalpartsran)/count(defect_code1)" setup.

i am thinking that i will do what i did with a previous database report
problem. i had setup a website where the database could be downloaded along
with snapshots of what the reports are looking like. (did this for a
different database)
don't know if that will help you get a better understanding of what i am
trying to do or not. will post back later today, when the site is up...

Hold off with the web stuff. I really don't have time to
dig into your database.

I think I might be getting a better grip on the issue of the
parts ran total now. The parts data will have multiple
defect records for each shift, but the parts ran is for the
part, not the defect (report detail). If that's correct,
then the parts footer section should have a text box named
txtRunningPartsRan bound to the sumoftotalpartsran field
(the same as the Total Sorted text box). Set this new text
box's RunningSum property to Over Group. Then the Shift
footer section's text box can display the correct(?) total
by using the expression =txtRunningPartsRan

Give that a try and let's see if we're any closer.

 

[jkendrick75]

i had to make one change to what you suggested in your last post. the text
box txtRunningPartsRan running sum property had to be changed to over all
(instead of over group). everything else works great. thanks so much for
your help.