Do I need to export to Excel?

[BruceM]

First, the general explanation. I have a crosstab query that counts the
number of parts processed per month. Any one of several named processes may
be used. The result may look like this:

Process Jan. Feb.
-------- ---- ----
Proc1 200 150
Proc2 100 120

I have another crosstab query that counts the number of parts reworked per
month per process:

Process Jan. Feb.
-------- ---- ----
Proc1 20 5
Proc2 10 0

I can base a subform on each query, place both subforms onto a main form,
and calculate the percentage in an unbound text box on the main form. For
instance, in January 20 out of 200 parts that received Proc1 were reworked,
so the percentage of parts that went straight through without rework is 90%.

However, this seems rather an awkward approach. Also, I would like to
generate charts (graphs), and I don't think this method will get me there.

What I would like is either to combine the information into one query or by
some other means streamline the process. I am including some technical
information below. I can provide more, or clarify, as needed, but I'm not
quite sure what is needed.

If what I am attempting is not practical in Access, I would appreciate
hearing about it so that I can make plans to export to Excel, where graph
making is fairly straightforward.

Here is the SQL for the 1) total number of parts and 2) reworked parts. It
is limited to Jan and Feb for now. Also, there is only one Process per Plan
for now.

TRANSFORM Sum(tblJob.PartQty)
AS SumOfPartQty
SELECT tblProcess.ProcName
FROM tblProcess
INNER JOIN ((tblPlan
INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID_PlanProc)
INNER JOIN tblJob ON tblPlan.PlanID = tblJob.PlanID_Job)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID_PlanProc
GROUP BY tblProcess.ProcName
PIVOT Format([Jobdate],"mmm") In ("Jan","Feb");

TRANSFORM Sum(tjctRework.QtyReworked)
AS SumOfQtyReworked
SELECT tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblJob
INNER JOIN (tblOp
INNER JOIN ((tblPlan
INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID_PlanProc)
INNER JOIN (tjctPlanOp
INNER JOIN tjctRework
ON tjctPlanOp.PlanOpID = tjctRework.PlanOpID_Rework)
ON tblPlan.PlanID = tjctPlanOp.PlanID_PlanOp)
ON tblOp.OpID = tjctPlanOp.OpID_PlanOp)
ON tblJob.JobID = tjctRework.JobID_Rework)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID_PlanProc
GROUP BY tblProcess.ProcName
PIVOT Format([Jobdate],"mmm") In ("Jan","Feb");

Tables:
tblPlan
PlanID (PK)
PlanNum

tblProcess
ProcessID (PK)
ProcessName

tjctProcPlan (junction table)
ProcPlanID (PK)
PlanID_PlanProc (FK to tblPlan)
ProcessID_PlanProc (FK to tblProcess)

tblOp
OperationID (PK)
OpName

tjctPlanOp (junction table)
PlanOpID (PK)
PlanID_PlanOp (FK to tblPlan)
OpID_PlanOp (FK to tblOp)
OpNumber (line item number in a particular Plan -- 10, 20, 30, etc.)

tblJob
JobID (PK)
JobNum
JobDate

tjctRework (junction table)
ReworkID (PK)
JobID_Rework (FK to tblJob)
PlanOpID_Rework (FK to tjctPlanOp)

One Plan may have many Processes, Operations, and Jobs performed according
to the Plan. The reverse is also true: Processes, Operations, and Jobs may
be associated with several Plans. Again, there is just one Process per Plan
for now, but that could change, so the structure is in place for
many-to-many.