J
jkendrick75
i have a table with the following design
ID Date PartNum DCode1 DQty1 DCode2 DQty2 DCode3 DQty3 Other TtlSort
=====================================================
5 6/30/04 012009-732 1 6 4 5 2 4 7
288
6 6/30/04 953399 9 8 12 5 3 3 0 1600
7 6/30/04 953398 22 4 26 3 1 1
0 2400
8 6/30/04 060353-2090 27 18 12 9 15 7 4
3987
9 6/30/04 588238 0 0 0 0 0 0
0 3786
10 6/30/04 953399 8 10 9 6 0 0
0 3786
11 6/30/04 833694 0 0 0 0 0 0 0 106
12 6/30/04 953237 0 0 0 0 0 0
0 14064
13 6/30/04 953399 10 18 20 9 3 4
8 2050
now i realize this is not the best way to create a table, but my supervisor
wanted it done this way. now what he is wanting is report that looks like
Between [StartDate] and [EndDate]
=PartNumber Header======================================
Part Number [PartNum]
=Details Section=========================================
Defect Code Defect Quantitiy DefectPercentage
[defectcode] [defectquantity] [PercofDefectQuantity]
=Part Number Footer======================================
Total sorted: [sumofTotalSort] Total Defects: [sumofDefects]
Percentage of Defects: [DefectPercentage]
i want the Details Section part to only include defect codes for however
many different codes for the particular part number for the given date range
(if there are 5 different DCodes for a part over a date range, then there is
a list of 5 DCodes in the details section of the report. if there are 10
different DCodes for a part within the given date range, then there is a list
of 10 DCodes in the details section of the report). i want the defects sorted
through each of the defect code fields and then summed for that defect for
the date range.
e.g. (What the report should look like, taken from data above)
Between 6/30/2004 and 7/1/2004
Part Number: 95399
Defect Codes Defect Quantity Defect Percentage
9 14 19.72
12 5 7.04
3 7 9.86
8 10 14.08
10 18 25.35
20 9 12.68
Other 8 11.27
Total Sorted: 7436 Total Defects: 71
Percentage of Defects: .95
Notice that the quantites for Defect codes 9 and 3 were totaled, even though
code 9 was under the DCode1 field in one record and DCode2 field in another
record. the defect code 3 was in the same field (DCode3) both times.
i know that there is some sorting to do but i have no idea where to start.
like i said previously, this design (for the table and the report) is what my
supervisor wants, so i do what i can. i won't be able to change the design
of the table, so i am kinda stuck with what's there.
thanks in advance for any help anybody is able to give, and happy holidays
to everyone.
ID Date PartNum DCode1 DQty1 DCode2 DQty2 DCode3 DQty3 Other TtlSort
=====================================================
5 6/30/04 012009-732 1 6 4 5 2 4 7
288
6 6/30/04 953399 9 8 12 5 3 3 0 1600
7 6/30/04 953398 22 4 26 3 1 1
0 2400
8 6/30/04 060353-2090 27 18 12 9 15 7 4
3987
9 6/30/04 588238 0 0 0 0 0 0
0 3786
10 6/30/04 953399 8 10 9 6 0 0
0 3786
11 6/30/04 833694 0 0 0 0 0 0 0 106
12 6/30/04 953237 0 0 0 0 0 0
0 14064
13 6/30/04 953399 10 18 20 9 3 4
8 2050
now i realize this is not the best way to create a table, but my supervisor
wanted it done this way. now what he is wanting is report that looks like
Between [StartDate] and [EndDate]
=PartNumber Header======================================
Part Number [PartNum]
=Details Section=========================================
Defect Code Defect Quantitiy DefectPercentage
[defectcode] [defectquantity] [PercofDefectQuantity]
=Part Number Footer======================================
Total sorted: [sumofTotalSort] Total Defects: [sumofDefects]
Percentage of Defects: [DefectPercentage]
i want the Details Section part to only include defect codes for however
many different codes for the particular part number for the given date range
(if there are 5 different DCodes for a part over a date range, then there is
a list of 5 DCodes in the details section of the report. if there are 10
different DCodes for a part within the given date range, then there is a list
of 10 DCodes in the details section of the report). i want the defects sorted
through each of the defect code fields and then summed for that defect for
the date range.
e.g. (What the report should look like, taken from data above)
Between 6/30/2004 and 7/1/2004
Part Number: 95399
Defect Codes Defect Quantity Defect Percentage
9 14 19.72
12 5 7.04
3 7 9.86
8 10 14.08
10 18 25.35
20 9 12.68
Other 8 11.27
Total Sorted: 7436 Total Defects: 71
Percentage of Defects: .95
Notice that the quantites for Defect codes 9 and 3 were totaled, even though
code 9 was under the DCode1 field in one record and DCode2 field in another
record. the defect code 3 was in the same field (DCode3) both times.
i know that there is some sorting to do but i have no idea where to start.
like i said previously, this design (for the table and the report) is what my
supervisor wants, so i do what i can. i won't be able to change the design
of the table, so i am kinda stuck with what's there.
thanks in advance for any help anybody is able to give, and happy holidays
to everyone.