Consolidate rows

[BruceM]

This is the SQL for a select (Totals) query that shows me the number of
parts per category that have been processed according to a particular
Technical Plan:

ProcName PartCat QtySum PartNum
----------- -------- -------- --------
Plate Gear 100 12345
Plate Gear 50 54321

This listing shows two jobs of 50 parts each (PartNum 12345) and one job of
50 parts of 54321. The two jobs for PartNum 12345 were consolidated into
one row. What I want is for all three jobs to be consolidated into one row
for Plating jobs on Gears:

ProcName PartCat QtySum
----------- -------- --------
Plate Gear 150

PartNum is in the first example for clarity. In practice I don't need the
PartNum to show, but whether it shows or not, the quantities are separated
as shown in the first example. How do I tell the query to sum the PartQty
for all of the Plate jobs on Gears? Here is the SQL:

SELECT tblProcess.ProcName, tblPart.PartCat,
DSum("[PartQty]","[tblJob]","[PartID_Job] = " & [PartID] & " AND
[PlanID_Job] = " & [PlanID])
AS QtySum, tblPart.PartNum
FROM tblPart
INNER JOIN ((tblTechPlan
INNER JOIN tblJob
ON tblTechPlan.PlanID = tblJob.PlanID_Job)
INNER JOIN (tblProcess
INNER JOIN tjctPlanProc
ON tblProcess.ProcessID = tjctPlanProc.ProcessID_PlanProc)
ON tblTechPlan.PlanID = tjctPlanProc.PlanID_PlanProc)
ON tblPart.PartID = tblJob.PartID_Job
GROUP BY tblProcess.ProcName, tblPart.PartCat,
DSum("[PartQty]","[tblJob]","[PartID_Job] = " & [PartID] & " AND
[PlanID_Job] = " & [PlanID]), tblPart.PartNum
ORDER BY tblProcess.ProcName, tblPart.PartCat;

I also tried making a query (qryCategory) from tblPart:

SELECT tblPart.PartID, tblPart.PartCat
FROM tblPart;

and substituting qryCategory.PartID and qryCategory.PartCat for
tblPart.PartID and tblPart.PartCat as needed, but still the same result
because the joining field (PartID) is different when the part numbers are
different.

 

[BruceM]

By the way, I can get the desired result by making a Select query from the
query I described in the original posting (I will call it qryCount):

SELECT qryCount.ProcName, qryCount.PartCat, Sum([QtySum]) AS QtyTotal
FROM qryCount
GROUP BY qryCount.ProcName, qryCount.PartCat;

It strikes me as clumsy to pile queries on top of each other like this, but
if it's an OK way to do it I'll move on to the next part of the project.

 

[John Spencer]

Would the following give you what you want.

SELECT tblProcess.ProcName
, tblPart.PartCat
, SUM (tblJob.PartQty) as QtySum
FROM tblPart
INNER JOIN ((tblTechPlan
INNER JOIN tblJob
ON tblTechPlan.PlanID = tblJob.PlanID_Job)
INNER JOIN (tblProcess
INNER JOIN tjctPlanProc
ON tblProcess.ProcessID = tjctPlanProc.ProcessID_PlanProc)
ON tblTechPlan.PlanID = tjctPlanProc.PlanID_PlanProc)
ON tblPart.PartID = tblJob.PartID_Job
GROUP BY tblProcess.ProcName, tblPart.PartCat
ORDER BY tblProcess.ProcName, tblPart.PartCat

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[BruceM]

Yes, thank you, that did it exactly. Curiously, in my workaround I used Sum
instead of DSum, but for some reason I was stuck on DSum in the original
query. I think I understand that Sum is the sum of the number field
(PartQty) for all records that fall within the group, so grouping by QtySum
is not necessary (nor is it possible when Sum is used rather than DSum).
Like so many things, it's pretty clear once I see it.

Would the following give you what you want.

SELECT tblProcess.ProcName
, tblPart.PartCat
, SUM (tblJob.PartQty) as QtySum
FROM tblPart
INNER JOIN ((tblTechPlan
INNER JOIN tblJob
ON tblTechPlan.PlanID = tblJob.PlanID_Job)
INNER JOIN (tblProcess
INNER JOIN tjctPlanProc
ON tblProcess.ProcessID = tjctPlanProc.ProcessID_PlanProc)
ON tblTechPlan.PlanID = tjctPlanProc.PlanID_PlanProc)
ON tblPart.PartID = tblJob.PartID_Job
GROUP BY tblProcess.ProcName, tblPart.PartCat
ORDER BY tblProcess.ProcName, tblPart.PartCat

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

This is the SQL for a select (Totals) query that shows me the number of
parts per category that have been processed according to a particular
Technical Plan:

ProcName PartCat QtySum PartNum
----------- -------- -------- --------
Plate Gear 100 12345
Plate Gear 50 54321

This listing shows two jobs of 50 parts each (PartNum 12345) and one job
of 50 parts of 54321. The two jobs for PartNum 12345 were consolidated
into one row. What I want is for all three jobs to be consolidated into
one row for Plating jobs on Gears:

ProcName PartCat QtySum
----------- -------- --------
Plate Gear 150

PartNum is in the first example for clarity. In practice I don't need
the PartNum to show, but whether it shows or not, the quantities are
separated as shown in the first example. How do I tell the query to sum
the PartQty for all of the Plate jobs on Gears? Here is the SQL:

SELECT tblProcess.ProcName, tblPart.PartCat,
DSum("[PartQty]","[tblJob]","[PartID_Job] = " & [PartID] & " AND
[PlanID_Job] = " & [PlanID])
AS QtySum, tblPart.PartNum
FROM tblPart
INNER JOIN ((tblTechPlan
INNER JOIN tblJob
ON tblTechPlan.PlanID = tblJob.PlanID_Job)
INNER JOIN (tblProcess
INNER JOIN tjctPlanProc
ON tblProcess.ProcessID = tjctPlanProc.ProcessID_PlanProc)
ON tblTechPlan.PlanID = tjctPlanProc.PlanID_PlanProc)
ON tblPart.PartID = tblJob.PartID_Job
GROUP BY tblProcess.ProcName, tblPart.PartCat,
DSum("[PartQty]","[tblJob]","[PartID_Job] = " & [PartID] & " AND
[PlanID_Job] = " & [PlanID]), tblPart.PartNum
ORDER BY tblProcess.ProcName, tblPart.PartCat;

I also tried making a query (qryCategory) from tblPart:

SELECT tblPart.PartID, tblPart.PartCat
FROM tblPart;

and substituting qryCategory.PartID and qryCategory.PartCat for
tblPart.PartID and tblPart.PartCat as needed, but still the same result
because the joining field (PartID) is different when the part numbers are
different.