B
BruceM
A brief description of the two-part situation:
First Part:
Jobs are performed according to Process Plans. Each Plan consists of
several operations picked from a list of possible operations performed at
the facility. Each Plan has several operations, and each operation may be
associated with several plans (for example, in a machine shop many Plans
include a Grind operation). Also, some operations (such as Grind) may occur
several times in a single Plan. The tables are tblPlan and tblOp. The
junction table is tjctPlanOp.
tblPlan
PlanID (PK)
PlanNumber
tblOp
OpID (PK)
OpDescription
tjctPlanOp
PlanOpID (PK)
PlanID (FK)
OpID (FK)
LineNumber (10, 20, etc. to correspond to line numbers on published
Plan)
A totals query counts the number of times each operation is performed in
each Plan:
SELECT Count(tjctPlanOp.OpID) AS CountOfOpID, tblOp.OpDescription,
tblPlan.PlanNumber
FROM tblOp
INNER JOIN (tblPlan INNER JOIN tjctPlanOp
ON tblPlan.PlanID = tjctPlanOp.PlanID)
ON tblOp.OpID = tjctPlanOp.OpID
GROUP BY tblOp.OpDescription, tblPlan.PlanNumber;
It looks like this when run:
Count...........Operation.............PlanNumber
2..................Grind.....................1234
1..................Polish....................1234
and so forth through all of the PlanNumbers.
PlanNumber and OpDescription are unique values, but I am not using them as
the PK because numbering and naming systems may change.
Second Part:
Job Information is stored in tblJob:
tblJob
JobID (PK)
PlanID (FK)
JobNumber
JobDate
Quantity
A Totals query counts the number of Jobs processed according to each Plan:
SELECT Count(tblJob.JobID) AS CountOfJobID, tblPlan.PlanNum
FROM tblPlan
INNER JOIN tblJob
ON tblPlan.PlanID = tblJob.PlanID
GROUP BY tblPlan.PlanNum;
Limiting the question to specific examples, I have a count of Jobs being
performed according to Plan 1234, and a count of the Grind operation in Plan
1234. If the Grind operation appears twice in Plan 1234, and if three Jobs
were performed according to Plan 1234, I need to know that the Grind
operation was performed 6 times total. How can I combine either the queries
or the query results so that a report can be generated showing that Grind
was performed 6 times total?
This is just a piece of the project, and I don't expect the "why"
necessarily to be clear. The question is how to combine the counts, if
possible, when the two queries have tblPlan in common.
First Part:
Jobs are performed according to Process Plans. Each Plan consists of
several operations picked from a list of possible operations performed at
the facility. Each Plan has several operations, and each operation may be
associated with several plans (for example, in a machine shop many Plans
include a Grind operation). Also, some operations (such as Grind) may occur
several times in a single Plan. The tables are tblPlan and tblOp. The
junction table is tjctPlanOp.
tblPlan
PlanID (PK)
PlanNumber
tblOp
OpID (PK)
OpDescription
tjctPlanOp
PlanOpID (PK)
PlanID (FK)
OpID (FK)
LineNumber (10, 20, etc. to correspond to line numbers on published
Plan)
A totals query counts the number of times each operation is performed in
each Plan:
SELECT Count(tjctPlanOp.OpID) AS CountOfOpID, tblOp.OpDescription,
tblPlan.PlanNumber
FROM tblOp
INNER JOIN (tblPlan INNER JOIN tjctPlanOp
ON tblPlan.PlanID = tjctPlanOp.PlanID)
ON tblOp.OpID = tjctPlanOp.OpID
GROUP BY tblOp.OpDescription, tblPlan.PlanNumber;
It looks like this when run:
Count...........Operation.............PlanNumber
2..................Grind.....................1234
1..................Polish....................1234
and so forth through all of the PlanNumbers.
PlanNumber and OpDescription are unique values, but I am not using them as
the PK because numbering and naming systems may change.
Second Part:
Job Information is stored in tblJob:
tblJob
JobID (PK)
PlanID (FK)
JobNumber
JobDate
Quantity
A Totals query counts the number of Jobs processed according to each Plan:
SELECT Count(tblJob.JobID) AS CountOfJobID, tblPlan.PlanNum
FROM tblPlan
INNER JOIN tblJob
ON tblPlan.PlanID = tblJob.PlanID
GROUP BY tblPlan.PlanNum;
Limiting the question to specific examples, I have a count of Jobs being
performed according to Plan 1234, and a count of the Grind operation in Plan
1234. If the Grind operation appears twice in Plan 1234, and if three Jobs
were performed according to Plan 1234, I need to know that the Grind
operation was performed 6 times total. How can I combine either the queries
or the query results so that a report can be generated showing that Grind
was performed 6 times total?
This is just a piece of the project, and I don't expect the "why"
necessarily to be clear. The question is how to combine the counts, if
possible, when the two queries have tblPlan in common.