B
BruceM
I have a table in which I enter Job information about machine parts that are
to be coated. It is related to a Parts table (each Part can be the subject
of many Jobs). The Part record includes a category (Gear, Spindle, etc.), a
specific Part Name, and a Part Number:
PartNumber Category PartName
------------ ---------- ----------
123 Gear Big Gear
124 Gear Small Gear
221 Spindle Steel Spindle
The Job table includes a field for the quantity of parts processed
(typically about 100). From these tables I can devise a query grouped by
Category (from the Parts table) and Quantity (from theJob table). If there
are three 100-piece Jobs, one for each of the Parts listed above, I can
devise a query that includes the Category field (Group By) and the Quantity
field (Sum). This yields the following:
Category Quantity
--------- --------
Gear 200
Spindle 100
There is also a table for rework. This is to record the quantity of parts
that needed to have a processing step repeated. Since there are several
processing steps, there may be several separate instances where rework is
needed. For this reason the Job table is related one-to-many to the Rework
table: each Job may have several Rework episodes. If one of the Jobs to
process a gear had two 10-part rework episodes and the other had one, and
the job to process a spindle had one rework episode, it should look like
this:
Category Rework
--------- --------
Gear 30
Spindle 10
The trouble is that I want to group the records by month, which means I
cannot combine these results. Since one of the Gear jobs has two rework
records, that job appears twice in any attempt I can make to combine the Job
quantity with the Rework total. This means it shows that 300 Gears were
processed, when the actual number was 200.
I have "solved" the problem by writing the subform total to the Job record,
but I do not like doing it that way. I would be interested in knowing if in
general there is a way to total the subform Rework quantities in such a way
that I can calculate a percentage of the main form records. I can do it
record by record, but I need aggregate numbers.
to be coated. It is related to a Parts table (each Part can be the subject
of many Jobs). The Part record includes a category (Gear, Spindle, etc.), a
specific Part Name, and a Part Number:
PartNumber Category PartName
------------ ---------- ----------
123 Gear Big Gear
124 Gear Small Gear
221 Spindle Steel Spindle
The Job table includes a field for the quantity of parts processed
(typically about 100). From these tables I can devise a query grouped by
Category (from the Parts table) and Quantity (from theJob table). If there
are three 100-piece Jobs, one for each of the Parts listed above, I can
devise a query that includes the Category field (Group By) and the Quantity
field (Sum). This yields the following:
Category Quantity
--------- --------
Gear 200
Spindle 100
There is also a table for rework. This is to record the quantity of parts
that needed to have a processing step repeated. Since there are several
processing steps, there may be several separate instances where rework is
needed. For this reason the Job table is related one-to-many to the Rework
table: each Job may have several Rework episodes. If one of the Jobs to
process a gear had two 10-part rework episodes and the other had one, and
the job to process a spindle had one rework episode, it should look like
this:
Category Rework
--------- --------
Gear 30
Spindle 10
The trouble is that I want to group the records by month, which means I
cannot combine these results. Since one of the Gear jobs has two rework
records, that job appears twice in any attempt I can make to combine the Job
quantity with the Rework total. This means it shows that 300 Gears were
processed, when the actual number was 200.
I have "solved" the problem by writing the subform total to the Job record,
but I do not like doing it that way. I would be interested in knowing if in
general there is a way to total the subform Rework quantities in such a way
that I can calculate a percentage of the main form records. I can do it
record by record, but I need aggregate numbers.