sorting and summing across different fields

[Guest]

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.

 

[Ken Snell [MVP]]

Perhaps, as your supervisor is telling you "how" to design the table, he/she
can design your query for you?

Your table structure is not good... tell your supervisor this. You also can
tell him/her that his/her desire for this table structure is going to make
your work about five times harder than it has to be to get the report.

Now, what you'll need to do with this "atrocious" table structure is to
create a UNION query that will normalize the data for you. From this union
query, you then can write a more "normal" query that will give you the
report that you seek.

What you need to do for the union query is to make a separate record for
each DCode# / DQty# pair for each ID:

SELECT ID, Date, PartNum, DCode1, DQty1, TtlSort
FROM MySupervisorsTable
UNION
SELECT ID, Date, PartNum, DCode2, DQty2, TtlSort
FROM MySupervisorsTable
UNION
SELECT ID, Date, PartNum, DCode3, DQty3, TtlSort
FROM MySupervisorsTable
UNION
SELECT ID, Date, PartNum, "Other" AS OtherCode, Other, TtlSort
FROM MySupervisorsTable;

The above query should be saved under some name. You now have a normalized
data set from your supervisor's table.

You now can write a totals query against this union query to get your
summations, just as if you had a proper table in the first place.
--

Ken Snell
<MS ACCESS MVP>

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.

 

[Guest]

thanks, this did the job for sorting the data,

happy holidays.

Perhaps, as your supervisor is telling you "how" to design the table, he/she
can design your query for you?

Your table structure is not good... tell your supervisor this. You also can
tell him/her that his/her desire for this table structure is going to make
your work about five times harder than it has to be to get the report.

Now, what you'll need to do with this "atrocious" table structure is to
create a UNION query that will normalize the data for you. From this union
query, you then can write a more "normal" query that will give you the
report that you seek.

What you need to do for the union query is to make a separate record for
each DCode# / DQty# pair for each ID:

SELECT ID, Date, PartNum, DCode1, DQty1, TtlSort
FROM MySupervisorsTable
UNION
SELECT ID, Date, PartNum, DCode2, DQty2, TtlSort
FROM MySupervisorsTable
UNION
SELECT ID, Date, PartNum, DCode3, DQty3, TtlSort
FROM MySupervisorsTable
UNION
SELECT ID, Date, PartNum, "Other" AS OtherCode, Other, TtlSort
FROM MySupervisorsTable;

The above query should be saved under some name. You now have a normalized
data set from your supervisor's table.

You now can write a totals query against this union query to get your
summations, just as if you had a proper table in the first place.
--

Ken Snell
<MS ACCESS MVP>

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.