Union Query of 2 Crosstab

[shawnjerome]

I am attempting to combine 2 crosstab queries and I keep getting the "Syntax error in TRANSFORM statement".

TRANSFORM Sum(tblPlanSavings.[PlanSavings]) AS SumOfPlanSavings
SELECT tblPlanSavings.[BusinessUnit], tblPlanSavings.[PlantName], tblPlanSavings.[Category], Sum(tblPlanSavings.[PlanSavings]) AS [Total Of PlanSavings]
FROM tblPlanSavings
GROUP BY tblPlanSavings.[BusinessUnit], tblPlanSavings.[PlantName], tblPlanSavings.[Category]


PIVOT Format([MonthYear],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec")
UNION ALL
TRANSFORM Sum(qryProcurementPlantSavingsAllMonths.Savings) AS SumOfSavings
SELECT qryProcurementPlantSavingsAllMonths.BusinessUnit, qryProcurementPlantSavingsAllMonths.PlantName, qryProcurementPlantSavingsAllMonths.Category, Sum(qryProcurementPlantSavingsAllMonths.Savings) AS [Total Of Savings]
FROM qryProcurementPlantSavingsAllMonths
GROUP BY qryProcurementPlantSavingsAllMonths.BusinessUnit, qryProcurementPlantSavingsAllMonths.PlantName, qryProcurementPlantSavingsAllMonths.Category
PIVOT Format([MonthYear],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

[June7]

Are you constructing the UNION in VBA or as an Access query object?

Maybe cannot do all in one sql. Try saving the crosstabs as two Access queries. Then UNION fields from the two queries.