Union Crosstab Queries

C

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
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top