Union 2 crosstab query's

[Chris]

Is it possible to union join 2 cross tab query's?

I can get the 2 query's to union when they are not cross tabbed. however
when I use the cross tabbed query's I get the error message;
'Characters found at the end of SQL Statement' - the characters are the word
UNION

please help

What I am trying.
TRANSFORM First(tblMotorValue.Value) AS FirstOfValue
SELECT tblLAsset.AssetType
FROM tblLAsset INNER JOIN ((tblLValueType INNER JOIN (tblMotorVehicle INNER
JOIN tblMotorValue ON tblMotorVehicle.motorID = tblMotorValue.MotorID) ON
tblLValueType.ValueTypeID = tblMotorValue.ValueType) INNER JOIN tblAssets ON
tblMotorVehicle.AssetID = tblAssets.AssetID) ON tblLAsset.AssetTypeID =
tblAssets.AssetType
GROUP BY tblAssets.AssetID, tblLAsset.AssetType
PIVOT tblLValueType.ValueType;

Union

TRANSFORM First(tblPropertyValue.Balance) AS FirstOfBalance
SELECT tblLAsset.AssetType
FROM tblLValueType INNER JOIN ((tblLAsset INNER JOIN (tblAssets INNER JOIN
tblHeritableProperty ON tblAssets.AssetID = tblHeritableProperty.AssetID) ON
tblLAsset.AssetTypeID = tblAssets.AssetType) INNER JOIN tblPropertyValue ON
tblHeritableProperty.HeritableProperty = tblPropertyValue.HeritableProperty)
ON tblLValueType.ValueTypeID = tblPropertyValue.ValueType
GROUP BY tblAssets.AssetID, tblLAsset.AssetType
PIVOT tblLValueType.ValueType;

 

[Michel Walsh]

Hi,

You can between two saved queries, but not inside the same SQL statement
where you use the operator TRANSFORM:



SELECT * FROM crosstab1
UNION ALL
SELECT * FROM crosstab1



as example, double all the rows from crosstab1.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Chris will probably also need to use the Column Heading property to define
the number and order of columns in each crosstab query as Union queries
require the same number of columns in each.