I am doing something obviously wrong, but I can't see it.each query returns the correct totals, but the union ALL querydoes not populate the table with the correct numbers. . . Its missing rows. . . USE [Admin]GOTRUNCATE TABLE [Dev].[dbo].[tmp_BRIDGE]GOSET ANSI_WARNINGS OFF DECLARE @YYYYMM INTSET @YYYYMM = 201305INSERT INTO [Dev].[dbo].[tmp_BRIDGE](FiscalPeriod, strDistrict, Location, Customer_Acct, Promo_Code, Nat_Local, [Contract], Modifier, Summary_Program, Product, YOR, MOR, NC, NL, PC, RB, SL, RL, RI, SN, TE, DN, NR, AW, XF, UN, LR, XN, Action_Amt, ER)SELECT fc.YYYYMM, rif.strDistrict, rif.Location, rif.Customer_Acct, rif.Promo_Code, rif.Local_National, rif.[Contract], rif.Modifier, sp.Common_Service_Plan, sp.ProdID, sum(IIF(fc.Fiscal_Month_Nbr = 1,rif.Annual_Amt,0)) AS "YOR", sum(rif.Annual_Amt) AS "MOR", 0 AS "NC",0 AS "NL",0 AS "PC",0 AS "RB",0 AS "SL",0 AS "RL",0 AS "RI",0 AS "SN",0 AS "TE", 0 AS "DN",0 AS "NR",0 AS "AW",0 AS "XF",0 AS "UN",0 AS "LR",0 AS "XN",0 AS "Action_Amt",0 AS "ER"FROM [dbo].[FISCAL_CALENDAR] fc INNER JOIN [dbo].[RIF_EXTRACT] rif ON fc.Prior_Period = rif.FiscalPeriod INNER JOIN [dbo].[SERVICE_PLAN_CODES] sp ON rif.Service_Program = sp.Svc_PlanWHERE fc.YYYYMM = @YYYYMMGROUP BY fc.YYYYMM, rif.strDistrict, rif.Location, rif.Customer_Acct, rif.Promo_Code, rif.Local_National, rif.[Contract], rif.Modifier, sp.Common_Service_Plan, sp.ProdIDUNION ALLSELECT rif.FiscalPeriod, rif.strDistrict, rif.Location, rif.Customer_Acct, rif.Promo_Code, rif.Local_National, rif.[Contract], rif.Modifier, sp.Common_Service_Plan, sp.ProdID, 0 AS "YOR", 0 AS "MOR", 0 AS "NC",0 AS "NL",0 AS "PC",0 AS "RB",0 AS "SL",0 AS "RL",0 AS "RI",0 AS "SN",0 AS "TE", 0 AS "DN",0 AS "NR",0 AS "AW",0 AS "XF",0 AS "UN",0 AS "LR",0 AS "XN",0 AS "Action_Amt",sum(rif.Annual_Amt) AS "ER"FROM [dbo].[RIF_EXTRACT] rif INNER JOIN [dbo].[SERVICE_PLAN_CODES] sp ON rif.Service_Program = sp.Svc_PlanWHERE rif.FiscalPeriod = @YYYYMMGROUP BY rif.FiscalPeriod, rif.strDistrict, rif.Location, rif.Customer_Acct, rif.Promo_Code, rif.Local_National, rif.[Contract], rif.Modifier, sp.Common_Service_Plan, sp.ProdIDUNION ALLSELECT act.FiscalPeriod, act.strDistrict, act.Location, act.Customer_Acct, act.Promo_Code, act.National_Local, act.[Contract], act.Modifier, sp.Common_Service_Plan, sp.ProdID, 0 AS "YOR", 0 AS "MOR", Sum(IIF(ac.Action_Code = 'NC',act.Annual_Action_Amt, 0)) AS "NC", Sum(IIF(ac.Action_Code = 'NL',act.Annual_Action_Amt, 0)) AS "NL", Sum(IIF(ac.Action_Code = 'PC',act.Annual_Action_Amt, 0)) AS "PC", Sum(IIF(ac.Action_Code = 'RB',act.Annual_Action_Amt, 0)) AS "RB", Sum(IIF(ac.Action_Code = 'SL',act.Annual_Action_Amt, 0)) AS "SL", Sum(IIF(ac.Action_Code = 'RL',act.Annual_Action_Amt, 0)) AS "RL", Sum(IIF(ac.Action_Code = 'RI',act.Annual_Action_Amt, 0)) AS "RI", Sum(IIF(ac.Action_Code = 'SN',act.Annual_Action_Amt, 0)) AS "SN", Sum(IIF(ac.Action_Code = 'TE',act.Annual_Action_Amt, 0)) AS "TE", Sum(IIF(ac.Action_Code = 'DN',act.Annual_Action_Amt, 0)) AS "DN", Sum(IIF(ac.Action_Code = 'NR',act.Annual_Action_Amt, 0)) AS "NR", Sum(IIF(ac.Action_Code = 'AW',act.Annual_Action_Amt, 0)) AS "AW", Sum(IIF(ac.Action_Code = 'XF',act.Annual_Action_Amt, 0)) AS "XF", Sum(IIF(ac.Action_Code = 'UN',act.Annual_Action_Amt, 0)) AS "UN", Sum(IIF(ac.Action_Code = 'LR',act.Annual_Action_Amt, 0)) AS "LF", Sum(IIF(ac.Action_Code = 'XN',act.Annual_Action_Amt, 0)) AS "XN", sum(act.Annual_Action_Amt) AS "Action_Amt", 0 AS "ER"FROM [dbo].[RIF_ACTIONS_EXTRACT] act INNER JOIN [dbo].[SERVICE_PLAN_CODES] sp ON act.Service_Program = sp.Svc_Plan INNER JOIN [dbo].[RIF_ACTION_CODES] ac ON act.[Action] = ac.[Action]WHERE act.FiscalPeriod = @YYYYMMGROUP BY act.FiscalPeriod, act.strDistrict, act.Location, act.Customer_Acct, act.Promo_Code, act.National_Local, act.[Contract], act.Modifier, sp.Common_Service_Plan, sp.ProdIDGO
thanks in advance. . .MS Access 20 years, SQL hack