Union All query help (SQL)

[Prime_Directive]

Hi,



I have two pivot queries I am trying to join into a single query. One shows attrition that happened for a total department and one shows attrition for a particular organization within the department. When I try to put a UNION ALL statement between the two queries I get an error that the "Transform" syntax is wrong. I suspect I am doing something incorrectly in how I have the aggregate functions laid out as this is the first time I have tried to combine two pivot queries. Can anybody please help? Thank you.

-Andrew

TRANSFORM Count(Terminations.[Standard Id])
SELECT 'Org Attrition'
FROM Terminations LEFT JOIN [Dept Taxonomy Mappings] ON Terminations.[Department Id]=[Dept Taxonomy Mappings].[Department ID]
WHERE Terminations.[Specific Org] = "Yes"
GROUP BY 'Org Attrition'
PIVOT Terminations.[Loc City Name] in ("City 1","City 2","City 3")


UNION ALL


TRANSFORM Count(Terminations.[Standard Id])
SELECT 'Total Attrition'
FROM Terminations LEFT JOIN [Dept Taxonomy Mappings] ON Terminations.[Department Id]=[Dept Taxonomy Mappings].[Department ID]
GROUP BY 'Total Attrition'
PIVOT Terminations.[Loc City Name] in ("City 1","City 2","City 3");

[June7]

These are crosstab queries created with query builder?

Try using alias field name in the crosstabs.

TRANSFORM Count(Terminations.[Standard Id]) AS CountID

Or save the crosstabs as query objects then build a UNION with those two objects.

[Prime_Directive]

Thanks, I tried using alias fields and that didn't do the trick. How would I go about saving the crosstabs as query objects? Appreciate your help.

[June7]

Just use the query builder - there is a wizard for crosstab. Save the query.