strSQL = "TRANSFORM IIF(SUM(ParCYMAmt) IS NULL, 0, SUM(ParCYMAmt)) AS TotalCost " & _
"SELECT HGRefNum, HMDetails, HDetails, SHDetails, " & _
"IIF(SUM(ParCYMAmt) IS NULL, 0, SUM(ParCYMAmt)) AS CYMAmt, IIF(SUM(ParCYYTDAmt) IS NULL, 0, SUM(ParCYYTDAmt)) AS CYYTDAmt, " & _
"IIF(SUM(ParPYMAmt) IS NULL, 0 , SUM(ParPYMAmt)) AS PYMAmt, IIF(SUM(ParPYYTDAmt) IS NULL, 0, SUM(ParPYYTDAmt)) AS PYYTDAmt FROM ( "
strSQL = strSQL & _
"SELECT t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails, " & _
"SUM(t0.TotCost) AS ParCYMAmt, 0 AS ParCYYTDAmt, 0 AS ParPYMAmt, 0 AS ParPYYTDAmt " & _
"FROM [Main Table] t0 LEFT JOIN [H Groupings] t1 ON t0.LedgerAcct = t1.LedgerAcct " & _
"WHERE t0.Period BETWEEN #" & StartDayM & "# AND #" & EndDayM & "# AND t0.CostCenter IN (" & AvCCQuery & ") " & _
"GROUP BY t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails "
strSQL = strSQL & _
"UNION ALL " & _
"SELECT t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails, " & _
"0 AS ParCYMAmt, SUM(t0.TotCost) AS ParCYYTDAmt, 0 AS ParPYMAmt, 0 AS ParPYYTDAmt " & _
"FROM [Main Table] t0 LEFT JOIN [H Groupings] t1 ON t0.LedgerAcct = t1.LedgerAcct " & _
"WHERE t0.Period BETWEEN #" & StartDayYTD & "# AND #" & EndDayM & "# AND t0.CostCenter IN (" & AvCCQuery & ") " & _
"GROUP BY t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails "
strSQL = strSQL & _
"UNION ALL " & _
"SELECT t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails, " & _
"0 AS ParCYMAmt, 0 AS ParCYYTDAmt, SUM(t0.TotCost) AS ParPYMAmt, 0 AS ParPYYTDAmt " & _
"FROM [Main Table] t0 LEFT JOIN [H Groupings] t1 ON t0.LedgerAcct = t1.LedgerAcct " & _
"WHERE t0.Period BETWEEN #" & PYStartDayM & "# AND #" & PYEndDayM & "# AND t0.CostCenter IN (" & AvCCQuery & ") " & _
"GROUP BY t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails "
strSQL = strSQL & _
"UNION ALL " & _
"SELECT t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails, " & _
"0 AS ParCYMAmt, 0 AS ParCYYTDAmt, 0 AS ParPYMAmt, SUM(t0.TotCost) AS ParPYYTDAmt " & _
"FROM [Main Table] t0 LEFT JOIN [H Groupings] t1 ON t0.LedgerAcct = t1.LedgerAcct " & _
"WHERE t0.Period BETWEEN #" & PYStartDayYTD & "# AND #" & PYEndDayM & "# AND t0.CostCenter IN (" & AvCCQuery & ") " & _
"GROUP BY t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails "
strSQL = strSQL & _
") x WHERE SHDetails IS NOT NULL GROUP BY HGRefNum, HMDetails, HDetails, SHDetails " & _
"PIVOT CostCenter"