incorrect totals for parts

J

jkendrick75

my report looks like the following,
-Page Header----------------------------------------------------------------
Defect Codes for Part [=[Forms]![frmbyparts]![cboPartnum].Value]
Shift Number =[Forms]![frmByParts]![cboshift].Value
Between [=[Forms]![frmByParts]![StartDate]] and
=[Forms]![frmByParts]![EndDate]
-MoldNumber Header-------------------------------------------------------
Mold Number [MoldNumber]
-PartNumber Header-------------------------------------------------------
[=Sum([sumofdefect1_quantity])](hidden attributes)
Defect Codes Defect Quantity/Code Percentage of Defects
-Detail-----------------------------------------------------------------------
[Defect_Code] [SumOfDefect1_Quantity]
[=([sumofdefect1_quantity]/[txtDefQtySum])*100]
-PageFooter----------------------------------------------------------------
-ReportFooter--------------------------------------------------------------
Total Parts Ran [SumOfTotalPartsRan] Total Good Parts
[=[txtpartsum]-[txtpartdefectsum]
Total Defects/Part [=Sum([sumofdefect1_quantity])] Defect Percentage
[=(Sum([sumofdefect1_quantity])/[SumOfTotalPartsRan])*100]

what the report should look like when i am using a sample of information,

Defect Codes for Part {88941193}
Shift Number {1} Between {01/01/2005} and {01/20/2005}
Mold Number {1051}
Defect Codes Defect Quantity/Code Percentage of Defects
{14} {274} {28.34}
{20} {241} {24.92}
{7} {155} {16.03}
{Other} {99} {10.24}
{16} {90} {9.31}
{3} {52} {5.38}
{1} {32} {3.31}
{6} {22} {2.28}
{17} {2} {0.21}
{0} {0} {0.00}
Total Parts Ran {10519} Total Good Parts {9554}
Total Defects/Part {967} Defect Percentage {9.19}

i am getting for the total parts ran {4007} so the total good parts and
defect percentage is off. however i am getting the correct number for the
total defects/Part.this reports recordsource is a query called
qryNormalizedDefects
the sql for this query is
SELECT qryDefectsSorted.MoldNumber, Sum(qryDefectsSorted.Defect1_Quantity)
AS SumOfDefect1_Quantity, Sum(qryDefectsSorted.totalpartsran) AS
SumOfTotalPartsRan, qryDefectsSorted.Defect_Code1, qryDefectsSorted.PartNumber
FROM qryDefectsSorted
WHERE
(((qryDefectsSorted.PartNumber)=[forms]![frmbyparts]![cboPartNum].[value])
AND ((qryDefectsSorted.Date) Between [forms]![frmByParts]![startdate] And
[forms]![frmByParts]![enddate]) AND
((qryDefectsSorted.Shift)=[forms]![frmByParts]![cboShift].[value]))
GROUP BY qryDefectsSorted.MoldNumber, qryDefectsSorted.Defect_Code1,
qryDefectsSorted.PartNumber
HAVING
(((qryDefectsSorted.PartNumber)=[forms]![frmbyparts]![cboPartNum].[value]));

this query depends on another query to get the data for defect_code1 as well
as a couple of other items. this query is a union query called
qryDefectsSorted
the SQL for this query is
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code1, Defect1_Quantity, totalpartsran
FROM [TBL defect count]
UNION
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code2, Defect2_Quantity, totalpartsran
FROM [TBL defect count]
UNION
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code3, Defect3_Quantity, totalpartsran
FROM [TBL defect count]
UNION SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
"Other" AS OtherCode, Other, totalpartsran
FROM [TBL defect count];

when i run the query qryNormalizedDefects by itself i get the following
output in
data sheet view (column names are shortened due to space)
SumOfTotPtsRan|SumOfDef1Qty|MoldNum|DefCode1|PrtNum
4255 | 0 | 1051 | 0 |88941193
1574 | 32 | 1051 | 1 |88941193
4007 | 274 | 1051 | 14 |88941193
1863 | 90 | 1051 | 16 |88941193
2257 | 2 | 1051 | 17 |88941193
7599 | 241 | 1051 | 20 |88941193
1863 | 52 | 1051 | 3 |88941193
570 | 22 | 1051 | 6 |88941193
3921 | 155 | 1051 | 7 |88941193
10519 | 99 | 1051 | Other |88941193

so as you can see i am getting the correct number of 10519 when the query is
run, but i am not able to get it into the report for the total parts ran in
the footer section.
i apologize for this post being so long, but i wanted to give anybody who
tries to answer this as much info as possible right from the start.
thanks for taking a look and hopefully somebody can shed some light on this
for me.
 
D

Duane Hookom

I'll take another stab at this. Your union query will probably quadruple the
TotalPartsRan. Have you looked at the datasheet view of this query? What
happens if you use:
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code1, Defect1_Quantity, totalpartsran
FROM [TBL defect count]
UNION
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code2, Defect2_Quantity, 0
FROM [TBL defect count]
UNION
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code3, Defect3_Quantity, 0
FROM [TBL defect count]
UNION SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
"Other" AS OtherCode, Other, 0
FROM [TBL defect count];

--
Duane Hookom
MS Access MVP


jkendrick75 said:
my report looks like the following,
-Page
Header----------------------------------------------------------------
Defect Codes for Part [=[Forms]![frmbyparts]![cboPartnum].Value]
Shift Number =[Forms]![frmByParts]![cboshift].Value
Between [=[Forms]![frmByParts]![StartDate]] and
=[Forms]![frmByParts]![EndDate]
-MoldNumber Header-------------------------------------------------------
Mold Number [MoldNumber]
-PartNumber Header-------------------------------------------------------
[=Sum([sumofdefect1_quantity])](hidden attributes)
Defect Codes Defect Quantity/Code Percentage of Defects
-Detail-----------------------------------------------------------------------
[Defect_Code] [SumOfDefect1_Quantity]
[=([sumofdefect1_quantity]/[txtDefQtySum])*100]
-PageFooter----------------------------------------------------------------
-ReportFooter--------------------------------------------------------------
Total Parts Ran [SumOfTotalPartsRan] Total Good Parts
[=[txtpartsum]-[txtpartdefectsum]
Total Defects/Part [=Sum([sumofdefect1_quantity])] Defect Percentage
[=(Sum([sumofdefect1_quantity])/[SumOfTotalPartsRan])*100]

what the report should look like when i am using a sample of information,

Defect Codes for Part {88941193}
Shift Number {1} Between {01/01/2005} and {01/20/2005}
Mold Number {1051}
Defect Codes Defect Quantity/Code Percentage of Defects
{14} {274} {28.34}
{20} {241} {24.92}
{7} {155} {16.03}
{Other} {99} {10.24}
{16} {90} {9.31}
{3} {52} {5.38}
{1} {32} {3.31}
{6} {22} {2.28}
{17} {2} {0.21}
{0} {0} {0.00}
Total Parts Ran {10519} Total Good Parts {9554}
Total Defects/Part {967} Defect Percentage {9.19}

i am getting for the total parts ran {4007} so the total good parts and
defect percentage is off. however i am getting the correct number for the
total defects/Part.this reports recordsource is a query called
qryNormalizedDefects
the sql for this query is
SELECT qryDefectsSorted.MoldNumber, Sum(qryDefectsSorted.Defect1_Quantity)
AS SumOfDefect1_Quantity, Sum(qryDefectsSorted.totalpartsran) AS
SumOfTotalPartsRan, qryDefectsSorted.Defect_Code1,
qryDefectsSorted.PartNumber
FROM qryDefectsSorted
WHERE
(((qryDefectsSorted.PartNumber)=[forms]![frmbyparts]![cboPartNum].[value])
AND ((qryDefectsSorted.Date) Between [forms]![frmByParts]![startdate] And
[forms]![frmByParts]![enddate]) AND
((qryDefectsSorted.Shift)=[forms]![frmByParts]![cboShift].[value]))
GROUP BY qryDefectsSorted.MoldNumber, qryDefectsSorted.Defect_Code1,
qryDefectsSorted.PartNumber
HAVING
(((qryDefectsSorted.PartNumber)=[forms]![frmbyparts]![cboPartNum].[value]));

this query depends on another query to get the data for defect_code1 as
well
as a couple of other items. this query is a union query called
qryDefectsSorted
the SQL for this query is
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code1, Defect1_Quantity, totalpartsran
FROM [TBL defect count]
UNION
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code2, Defect2_Quantity, totalpartsran
FROM [TBL defect count]
UNION
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code3, Defect3_Quantity, totalpartsran
FROM [TBL defect count]
UNION SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
"Other" AS OtherCode, Other, totalpartsran
FROM [TBL defect count];

when i run the query qryNormalizedDefects by itself i get the following
output in
data sheet view (column names are shortened due to space)
SumOfTotPtsRan|SumOfDef1Qty|MoldNum|DefCode1|PrtNum
4255 | 0 | 1051 | 0
|88941193
1574 | 32 | 1051 | 1
|88941193
4007 | 274 | 1051 | 14
|88941193
1863 | 90 | 1051 | 16
|88941193
2257 | 2 | 1051 | 17
|88941193
7599 | 241 | 1051 | 20 |88941193
1863 | 52 | 1051 | 3
|88941193
570 | 22 | 1051 | 6
|88941193
3921 | 155 | 1051 | 7
|88941193
10519 | 99 | 1051 | Other |88941193

so as you can see i am getting the correct number of 10519 when the query
is
run, but i am not able to get it into the report for the total parts ran
in
the footer section.
i apologize for this post being so long, but i wanted to give anybody who
tries to answer this as much info as possible right from the start.
thanks for taking a look and hopefully somebody can shed some light on
this
for me.
Click to expand...
 
J

jkendrick75

thanks, that worked great.

Duane Hookom said:
I'll take another stab at this. Your union query will probably quadruple the
TotalPartsRan. Have you looked at the datasheet view of this query? What
happens if you use:
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code1, Defect1_Quantity, totalpartsran
FROM [TBL defect count]
UNION
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code2, Defect2_Quantity, 0
FROM [TBL defect count]
UNION
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code3, Defect3_Quantity, 0
FROM [TBL defect count]
UNION SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
"Other" AS OtherCode, Other, 0
FROM [TBL defect count];

--
Duane Hookom
MS Access MVP


jkendrick75 said:
my report looks like the following,
-Page
Header----------------------------------------------------------------
Defect Codes for Part [=[Forms]![frmbyparts]![cboPartnum].Value]
Shift Number =[Forms]![frmByParts]![cboshift].Value
Between [=[Forms]![frmByParts]![StartDate]] and
=[Forms]![frmByParts]![EndDate]
-MoldNumber Header-------------------------------------------------------
Mold Number [MoldNumber]
-PartNumber Header-------------------------------------------------------
[=Sum([sumofdefect1_quantity])](hidden attributes)
Defect Codes Defect Quantity/Code Percentage of Defects
-Detail-----------------------------------------------------------------------
[Defect_Code] [SumOfDefect1_Quantity]
[=([sumofdefect1_quantity]/[txtDefQtySum])*100]
-PageFooter----------------------------------------------------------------
-ReportFooter--------------------------------------------------------------
Total Parts Ran [SumOfTotalPartsRan] Total Good Parts
[=[txtpartsum]-[txtpartdefectsum]
Total Defects/Part [=Sum([sumofdefect1_quantity])] Defect Percentage
[=(Sum([sumofdefect1_quantity])/[SumOfTotalPartsRan])*100]

what the report should look like when i am using a sample of information,

Defect Codes for Part {88941193}
Shift Number {1} Between {01/01/2005} and {01/20/2005}
Mold Number {1051}
Defect Codes Defect Quantity/Code Percentage of Defects
{14} {274} {28.34}
{20} {241} {24.92}
{7} {155} {16.03}
{Other} {99} {10.24}
{16} {90} {9.31}
{3} {52} {5.38}
{1} {32} {3.31}
{6} {22} {2.28}
{17} {2} {0.21}
{0} {0} {0.00}
Total Parts Ran {10519} Total Good Parts {9554}
Total Defects/Part {967} Defect Percentage {9.19}

i am getting for the total parts ran {4007} so the total good parts and
defect percentage is off. however i am getting the correct number for the
total defects/Part.this reports recordsource is a query called
qryNormalizedDefects
the sql for this query is
SELECT qryDefectsSorted.MoldNumber, Sum(qryDefectsSorted.Defect1_Quantity)
AS SumOfDefect1_Quantity, Sum(qryDefectsSorted.totalpartsran) AS
SumOfTotalPartsRan, qryDefectsSorted.Defect_Code1,
qryDefectsSorted.PartNumber
FROM qryDefectsSorted
WHERE
(((qryDefectsSorted.PartNumber)=[forms]![frmbyparts]![cboPartNum].[value])
AND ((qryDefectsSorted.Date) Between [forms]![frmByParts]![startdate] And
[forms]![frmByParts]![enddate]) AND
((qryDefectsSorted.Shift)=[forms]![frmByParts]![cboShift].[value]))
GROUP BY qryDefectsSorted.MoldNumber, qryDefectsSorted.Defect_Code1,
qryDefectsSorted.PartNumber
HAVING
(((qryDefectsSorted.PartNumber)=[forms]![frmbyparts]![cboPartNum].[value]));

this query depends on another query to get the data for defect_code1 as
well
as a couple of other items. this query is a union query called
qryDefectsSorted
the SQL for this query is
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code1, Defect1_Quantity, totalpartsran
FROM [TBL defect count]
UNION
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code2, Defect2_Quantity, totalpartsran
FROM [TBL defect count]
UNION
SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
Defect_Code3, Defect3_Quantity, totalpartsran
FROM [TBL defect count]
UNION SELECT ID, Date, MoldNumber, Shift, Plant, PartNumber, PressNumber,
"Other" AS OtherCode, Other, totalpartsran
FROM [TBL defect count];

when i run the query qryNormalizedDefects by itself i get the following
output in
data sheet view (column names are shortened due to space)
SumOfTotPtsRan|SumOfDef1Qty|MoldNum|DefCode1|PrtNum
4255 | 0 | 1051 | 0
|88941193
1574 | 32 | 1051 | 1
|88941193
4007 | 274 | 1051 | 14
|88941193
1863 | 90 | 1051 | 16
|88941193
2257 | 2 | 1051 | 17
|88941193
7599 | 241 | 1051 | 20 |88941193
1863 | 52 | 1051 | 3
|88941193
570 | 22 | 1051 | 6
|88941193
3921 | 155 | 1051 | 7
|88941193
10519 | 99 | 1051 | Other |88941193

so as you can see i am getting the correct number of 10519 when the query
is
run, but i am not able to get it into the report for the total parts ran
in
the footer section.
i apologize for this post being so long, but i wanted to give anybody who
tries to answer this as much info as possible right from the start.
thanks for taking a look and hopefully somebody can shed some light on
this
for me.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

need help with report 0
similar report, different database 0

Top