Problems with union query

[pseudonym44]

I am using four select queries joined by UNION to populate a recordsheet in Access 2007 as the basis for a report. (To save space, I'll just use two of them here.)

The recordsheet ends up with 6 columns - Group, Metric, Details, FY 2009, FY 2010, and FY 2011. Here's the first query, qryDrugSeizures_FYGrpXtab. It works fine by itself.

TRANSFORM Sum(Format([HQQuantity],"#,##0.000")) AS GrpTotal SELECT tblDrugSeizures.Group, "Drug Seizures" AS Metric, tblDrugSeizures.[Drug] AS Details
FROM tblDrugSeizures
WHERE (((tblDrugSeizures.DateSeized)>#9/30/2008#))
GROUP BY tblDrugSeizures.Group, "Drug Seizures", tblDrugSeizures.[Drug]
ORDER BY tblDrugSeizures.Group, tblDrugSeizures.[Drug]
PIVOT WhatFY([DateSeized]) In ("FY 2009","FY 2010", "FY 2011" );

Here's the second query - qryArrests_FYGrpXtab - it also works fine by itself.

TRANSFORM Count(tblArrests.PageNo) AS CountOfPageNo SELECT tblArrests.Group, "Arrests" AS Metric, "" AS Details
FROM tblArrests
WHERE (((tblArrests.ArrestDate)>#9/30/2008#))
GROUP BY tblArrests.Group, "Arrests", ""
ORDER BY tblArrests.Group
PIVOT WhatFY([ArrestDate]) In ("FY 2009","FY 2010", "FY 2011" );

This Union query also works fine:

Select * from qryDrugSeizures_FYGrpXtab
UNION
Select * from qryArrests_FYGrpXtab;

But when I take the two queries, join them with UNION, remove the first semi-colon and move the ORDER BY clause in the first query to the end, I get a "Syntax error in Transform Statment" error message with the word "UNION" highlighted. Here is the query I'd like to use, the one that gives the error:

TRANSFORM Sum(Format([HQQuantity],"#,##0.000")) AS GrpTotal SELECT tblDrugSeizures.Group, "Drug Seizures" AS Metric, tblDrugSeizures.[Drug] AS Details
FROM tblDrugSeizures
WHERE (((tblDrugSeizures.DateSeized)>#9/30/2008#))
GROUP BY tblDrugSeizures.Group, "Drug Seizures", tblDrugSeizures.[Drug]
PIVOT WhatFY([DateSeized]) In ("FY 2009","FY 2010", "FY 2011" )
UNION
TRANSFORM Count(tblArrests.PageNo) AS CountOfPageNo SELECT tblArrests.Group, "Arrests" AS Metric, "" AS Details
FROM tblArrests
WHERE (((tblArrests.ArrestDate)>#9/30/2008#))
GROUP BY tblArrests.Group, "Arrests", ""
PIVOT WhatFY([ArrestDate]) In ("FY 2009","FY 2010", "FY 2011" )
ORDER BY tblDrugSeizures.Group, Metric, Details;

I would like to use the elaborated SQL so I can use variables for the date in the Where clause and for the three columns for fiscal years. I've tried making changes in the ORDER BY clause, since that's the only one that changes from the separate SQL statements to the UNION statement. Any help would be appreciated. Thanks in advance.

 

[Norie]

Have you tried using a UNION query to return all the data then using that for the crosstab (TRANSFORM) query?