If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Consolidate rows
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. |
#2
|
|||
|
|||
Consolidate rows
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. "BruceM" wrote in message ... 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. |
#3
|
|||
|
|||
Consolidate rows
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" wrote in message ... 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. |
#4
|
|||
|
|||
Consolidate rows
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. "John Spencer" wrote in message ... 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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|