Percentage of Defects by Shift

[CevinMoses]

I have 3 shifts defined by the ID number of their foremen [Cast Forman
Clock]. Product 4620 can have several defects [Defect Code], of which "04"
is the one I am trying to analyze. I have been able to calculate the
percentage of Defect-04 / the total number of pieces for the month of
October. I can also calculate the percentage of Defect-04 each shift made /
the total number of pieces made by all three shifts. However, all three
shifts did not make the same number of pieces, and I would like to calculate
the percentage of the Defect-04 each shift made / the total pieces made BY
THAT SHIFT. My current code is below. CountShift was intended to count the
number of pieces that each shift made, but it returns the total number of
pieces made by all 3 shifts instead.

SELECT Oct06_4620.[Defect Code], Count(Oct06_4620.[Defect Code]) AS
CountGrade, (SELECT Count([Cast Foreman Clock]) From [Oct06_4620]) AS
CountShift, Count([Inspect Grade])/(SELECT Count([Inspect Grade]) From
[Oct06_4620]) AS Percentage, Oct06_4620.[Cast Foreman Clock]
FROM Oct06_4620
GROUP BY Oct06_4620.[Defect Code], Oct06_4620.[Cast Foreman Clock]
HAVING (((Oct06_4620.[Defect Code])="04"));

Thanks,
Cevin

 

[David F Cox]

(SELECT Count([Cast Foreman Clock]) From [Oct06_4620]),

I do not see any selection by shift here

 

[jlepack]

Could you show an example of what your data looks like? I don't see
any information for a quantity.

A row or two of your Oct06_4620 table should do nicely in helping to
determine the problem.

Cheers,
Jason Lepack

 

[CevinMoses]

[Serial No] [Inspect Grade] [Cast Foreman Clock] [Defect code]
ABC00001 C 12345
04
ABC00002 C 12345
07
ABC00003 C 54321
04

2 pieces were made under foreman #12345. 1 piece was made under foreman
#54321. I want to know that 100% of the pieces made under foreman 54321
receied a "C" grading because of defect code "04". Right now it's giving me
33%, a percentage of the total pieces made under all foremen, not just a
percentage of the pieces made under foreman 54321.

Hope that's clear enough. Thanks for the help.

-Cevin

Could you show an example of what your data looks like? I don't see
any information for a quantity.

A row or two of your Oct06_4620 table should do nicely in helping to
determine the problem.

Cheers,
Jason Lepack

I have 3 shifts defined by the ID number of their foremen [Cast Forman
Clock]. Product 4620 can have several defects [Defect Code], of which "04"
is the one I am trying to analyze. I have been able to calculate the
percentage of Defect-04 / the total number of pieces for the month of
October. I can also calculate the percentage of Defect-04 each shift made /
the total number of pieces made by all three shifts. However, all three
shifts did not make the same number of pieces, and I would like to calculate
the percentage of the Defect-04 each shift made / the total pieces made BY
THAT SHIFT. My current code is below. CountShift was intended to count the
number of pieces that each shift made, but it returns the total number of
pieces made by all 3 shifts instead.

SELECT Oct06_4620.[Defect Code], Count(Oct06_4620.[Defect Code]) AS
CountGrade, (SELECT Count([Cast Foreman Clock]) From [Oct06_4620]) AS
CountShift, Count([Inspect Grade])/(SELECT Count([Inspect Grade]) From
[Oct06_4620]) AS Percentage, Oct06_4620.[Cast Foreman Clock]
FROM Oct06_4620
GROUP BY Oct06_4620.[Defect Code], Oct06_4620.[Cast Foreman Clock]
HAVING (((Oct06_4620.[Defect Code])="04"));

Thanks,
Cevin

 

[jlepack]

A quick solution with three queries. If this doesn't suffice I'll look
at it again later.

Queries in SQL view with their results.

qry_defect_by_foreman:
SELECT Oct06_4620.[Inspect Grade], Oct06_4620.[Cast Foreman Clock],
Oct06_4620.[Defect code], Count(Oct06_4620.[Serial No]) AS DefectCount
FROM Oct06_4620
GROUP BY Oct06_4620.[Inspect Grade], Oct06_4620.[Cast Foreman Clock],
Oct06_4620.[Defect code];

Inspect Grade Cast Foreman Clock Defect code DefectCount
C 12345 04 1
C 12345 07 1
C 54321 04 1

qry_total_by_foreman:
SELECT Oct06_4620.[Cast Foreman Clock], Count(Oct06_4620.[Serial No])
AS TotalCount
FROM Oct06_4620
GROUP BY Oct06_4620.[Cast Foreman Clock];

Cast Foreman Clock TotalCount
12345 2
54321 1

qry_percentage:
SELECT qry_defect_by_foreman.[Cast Foreman Clock],
qry_defect_by_foreman.[Defect code], qry_defect_by_foreman.[Inspect
Grade], [DefectCount]/[TotalCount] AS Percentage
FROM qry_total_by_foreman INNER JOIN qry_defect_by_foreman ON
qry_total_by_foreman.[Cast Foreman Clock]=qry_defect_by_foreman.[Cast
Foreman Clock];

Cast Foreman Clock Defect code Inspect Grade Percentage
12345 04 C
50.00%
12345 07 C
50.00%
54321 04 C
100.00%

[Serial No] [Inspect Grade] [Cast Foreman Clock] [Defect code]
ABC00001 C 12345
04
ABC00002 C 12345
07
ABC00003 C 54321
04

2 pieces were made under foreman #12345. 1 piece was made under foreman
#54321. I want to know that 100% of the pieces made under foreman 54321
receied a "C" grading because of defect code "04". Right now it's giving me
33%, a percentage of the total pieces made under all foremen, not just a
percentage of the pieces made under foreman 54321.

Hope that's clear enough. Thanks for the help.

-Cevin

Could you show an example of what your data looks like? I don't see
any information for a quantity.

A row or two of your Oct06_4620 table should do nicely in helping to
determine the problem.

Cheers,
Jason Lepack

I have 3 shifts defined by the ID number of their foremen [Cast Forman
Clock]. Product 4620 can have several defects [Defect Code], of which "04"
is the one I am trying to analyze. I have been able to calculate the
percentage of Defect-04 / the total number of pieces for the month of
October. I can also calculate the percentage of Defect-04 each shift made /
the total number of pieces made by all three shifts. However, all three
shifts did not make the same number of pieces, and I would like to calculate
the percentage of the Defect-04 each shift made / the total pieces made BY
THAT SHIFT. My current code is below. CountShift was intended to count the
number of pieces that each shift made, but it returns the total number of
pieces made by all 3 shifts instead.

SELECT Oct06_4620.[Defect Code], Count(Oct06_4620.[Defect Code]) AS
CountGrade, (SELECT Count([Cast Foreman Clock]) From [Oct06_4620]) AS
CountShift, Count([Inspect Grade])/(SELECT Count([Inspect Grade]) From
[Oct06_4620]) AS Percentage, Oct06_4620.[Cast Foreman Clock]
FROM Oct06_4620
GROUP BY Oct06_4620.[Defect Code], Oct06_4620.[Cast Foreman Clock]
HAVING (((Oct06_4620.[Defect Code])="04"));

Thanks,
Cevin