Aggregate Results from Union query

[hsduk101]

I have 2 queries - with slightly different joins - with the same fields that I am union-ing. My results set give me 2 lines for one 'route'



QUESTION: How do i Aggregate the duplicated lines (I dont want to aggregate on Success and Count Prevent - these to be summed by group by)?

I think the solution is to write a Select statement outside this overall select statement - but I dont know how to write this in MS Access 2010

......ALSO - Can I used the results from the union query as a sub query into another query (Being designed in Query Designer)

(CODE BELOW)

SELECT qryRef_Routes_tbl.Ref_Route_Name, qryRef_Routes_tbl.Ref_Route_ID, tblFMS_Data.FM_FinYear, tblFMS_Data.FM_Period, tblPeriod_Lookup.PL_ID, Sum(IIf([FM_TrustNo]=0 And [FM_Incident]=25 And [Ref_Fault_Valid_Combo]=-1,1,0)) AS Success, Sum(IIf([FM_Incident]=25 And [Ref_Fault_Valid_Combo]=-1,1,0)) AS CountPreventFROM (((tblFMS_Data INNER JOIN qryRef_Routes_tbl ON tblFMS_Data.FM_Route = qryRef_Routes_tbl.Ref_Route_ID) INNER JOIN tblRCM_II_Assets ON tblFMS_Data.FM_EllipseNo = tblRCM_II_Assets.RI_Number) INNER JOIN tblPeriod_Lookup ON (tblFMS_Data.FM_FinYear = tblPeriod_Lookup.PL_FinYear) AND (tblFMS_Data.FM_Period = tblPeriod_Lookup.PL_PeriodNo)) INNER JOIN qryRef_Detectable_Failure_Valid_Combo ON (tblFMS_Data.FM_Component2 = qryRef_Detectable_Failure_Valid_Combo.Ref_Fault_Comp2_ID) AND (tblFMS_Data.FM_Component = qryRef_Detectable_Failure_Valid_Combo.Ref_Fault_Comp1_ID)
GROUP BY qryRef_Routes_tbl.Ref_Route_Name, qryRef_Routes_tbl.Ref_Route_ID, tblFMS_Data.FM_FinYear, tblFMS_Data.FM_Period, tblPeriod_Lookup.PL_ID
HAVING (((tblPeriod_Lookup.PL_ID) Between [Forms]![frmMainMenu]![PriorPeriodID] And ([Forms]![frmMainMenu]![PriorPeriodID]-12)));




UNION ALL


SELECT qryRef_Routes_tbl.Ref_Route_Name, qryRef_Routes_tbl.Ref_Route_ID, tblFMS_Data.FM_FinYear, tblFMS_Data.FM_Period, tblPeriod_Lookup.PL_ID, Sum(IIf([FM_TrustNo]=0 And [FM_Incident]=25 And [Ref_Fault_Valid_Combo]=-1,1,0)) AS Success, Sum(IIf([FM_Incident]=25 And [Ref_Fault_Valid_Combo]=-1,1,0)) AS CountPrevent
FROM (((tblFMS_Data INNER JOIN qryRef_Routes_tbl ON tblFMS_Data.FM_Route = qryRef_Routes_tbl.Ref_Route_ID) INNER JOIN tblPeriod_Lookup ON (tblFMS_Data.FM_FinYear = tblPeriod_Lookup.PL_FinYear) AND (tblFMS_Data.FM_Period = tblPeriod_Lookup.PL_PeriodNo)) INNER JOIN qryRef_Detectable_Failure_Valid_Combo ON (tblFMS_Data.FM_Component2 = qryRef_Detectable_Failure_Valid_Combo.Ref_Fault_Comp2_ID) AND (tblFMS_Data.FM_Component = qryRef_Detectable_Failure_Valid_Combo.Ref_Fault_Comp1_ID)) INNER JOIN tblRCM_II_Assets ON tblFMS_Data.FM_EllipseNo = tblRCM_II_Assets.RI_Pway_Number
GROUP BY qryRef_Routes_tbl.Ref_Route_Name, qryRef_Routes_tbl.Ref_Route_ID, tblFMS_Data.FM_FinYear, tblFMS_Data.FM_Period, tblPeriod_Lookup.PL_ID
HAVING (((tblPeriod_Lookup.PL_ID) Between [Forms]![frmMainMenu]![PriorPeriodID] And ([Forms]![frmMainMenu]![PriorPeriodID]-12)));

[pbaldy]

You can create a new query based on the union query. In design view, just add the query to the grid just like you would add a table (queries are on a separate tab).