using a footer total in details section

jkendrick75 wrote:
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.

"Marshall Barton" wrote:
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).

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?



"Marshall Barton" wrote:

jkendrick75 wrote:
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.

"Marshall Barton" wrote:
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 wrote:

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.

--
Marsh
MVP [MS Access]

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...




"Marshall Barton" wrote:

jkendrick75 wrote:

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.

--
Marsh
MVP [MS Access]

jkendrick75 wrote:

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.

--
Marsh
MVP [MS Access]

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.

"Marshall Barton" wrote:

jkendrick75 wrote:

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.

--
Marsh
MVP [MS Access]