Union Crosstab Queries

[Claudette Hennessy]

I saw a reference to unioning Xtab queries and thought it would solve my
problem, but no.

A report needs to list :

ShopName Spring Summer Fall
Comments
Out In Booth Out In Booth Out In
Booth

Dealer 1 yes no A21 yes no B45 yes yes C54
good dealer
Dealer2
Dealer3 no no yes yes D22 yes no
only summer

etc.

I can get a Xtab query to provide all the data for either Out or In but not
both. In the Union query below, SendContract stands for Out and
ContractReceived stands for in :

TRANSFORM First(qryContractChecklistRedDotForXtab.ContractReceived) AS
FirstOfContractReceived
SELECT qryContractChecklistRedDotForXtab.ShopName,
qryContractChecklistRedDotForXtab.BoothLocation,
qryContractChecklistRedDotForXtab.Comment
FROM qryContractChecklistRedDotForXtab
GROUP BY qryContractChecklistRedDotForXtab.ShopName,
qryContractChecklistRedDotForXtab.BoothLocation,
qryContractChecklistRedDotForXtab.Comment
PIVOT qryContractChecklistRedDotForXtab.ShowName
Union
TRANSFORM First(qryContractChecklistRedDotForXtab.SendContract) AS
FirstOfSendContract
SELECT qryContractChecklistRedDotForXtab.ShopName,
qryContractChecklistRedDotForXtab.BoothLocation,
qryContractChecklistRedDotForXtab.Comment
FROM qryContractChecklistRedDotForXtab
GROUP BY qryContractChecklistRedDotForXtab.ShopName,
qryContractChecklistRedDotForXtab.BoothLocation,
qryContractChecklistRedDotForXtab.Comment
PIVOT qryContractChecklistRedDotForXtab.ShowName;

I get no end of complaints from the system when I try to run this. How do
you Union X-tab queries?

Thank you,
Claudette Hennessy

 

[Jerry Whittle]

The TRANSFORM statement will nail you. Save both of the SQL statements then
run something like this:

SELECT * from qryClaudetteCrosstab1;
UNION ALL
SELECT * from qryClaudetteCrosstab2;

As you need the same number of columns in each part of the crosstab in the
same order, you probably need to look into the Column Headings property of
both crosstabs.