Joining multiple cross tab queries

[Sarah]

I have four crosstab queries which i am attempting to combine into one table
- the problem is that the informaiton seems to be duplicating when i attempt
to combine the four ct queries....please let me know what more information is
needed.

Ad Count CT Query
TRANSFORM Count(Sheet1.[AdOrder Number]) AS [CountOfAdOrder Number]
SELECT Sheet1.[Account Name], Sheet1.[Daily/Sunday]
FROM Sheet1
GROUP BY Sheet1.[Account Name], Sheet1.[Daily/Sunday]
PIVOT Sheet1.Year;

Page count CT Query
TRANSFORM Sum(Sheet1.[PRP Page Count]) AS [SumOfPRP Page Count]
SELECT Sheet1.[Account Name], Sheet1.[Daily/Sunday]
FROM Sheet1
GROUP BY Sheet1.[Account Name], Sheet1.[Daily/Sunday]
PIVOT Sheet1.Year;

Quantity CT Query
TRANSFORM Sum(Sheet1.[Preprint Count]) AS [SumOfPreprint Count]
SELECT Sheet1.[Account Name], Sheet1.[Daily/Sunday]
FROM Sheet1
GROUP BY Sheet1.[Account Name], Sheet1.[Daily/Sunday]
PIVOT Sheet1.Year;

Revenue CT Query
TRANSFORM Sum(Sheet1.[Gross Revenue]) AS [SumOfGross Revenue]
SELECT Sheet1.[Account Name], Sheet1.[Daily/Sunday]
FROM Sheet1
GROUP BY Sheet1.[Account Name], Sheet1.[Daily/Sunday]
PIVOT Sheet1.Year;

How can I combine the above four queries so that i have one table/query with:

Account Name Daily/Sunday Ad Count 08 Ad Count 07 Page Ct 08 Page Ct 07
Qty 08 Qty 07 Revenue08 Revenue 07

 

[pietlinden]

I have four crosstab queries which i am attempting to combine into one table
- the problem is that the informaiton seems to be duplicating when i attempt
to combine the four ct queries....please let me know what more information is
needed.

Ad Count CT Query
TRANSFORM Count(Sheet1.[AdOrder Number]) AS [CountOfAdOrder Number]
SELECT Sheet1.[Account Name], Sheet1.[Daily/Sunday]
FROM Sheet1
GROUP BY Sheet1.[Account Name], Sheet1.[Daily/Sunday]
PIVOT Sheet1.Year;

Page count CT Query
TRANSFORM Sum(Sheet1.[PRP Page Count]) AS [SumOfPRP Page Count]
SELECT Sheet1.[Account Name], Sheet1.[Daily/Sunday]
FROM Sheet1
GROUP BY Sheet1.[Account Name], Sheet1.[Daily/Sunday]
PIVOT Sheet1.Year;

Quantity CT Query
TRANSFORM Sum(Sheet1.[Preprint Count]) AS [SumOfPreprint Count]
SELECT Sheet1.[Account Name], Sheet1.[Daily/Sunday]
FROM Sheet1
GROUP BY Sheet1.[Account Name], Sheet1.[Daily/Sunday]
PIVOT Sheet1.Year;

Revenue CT Query
TRANSFORM Sum(Sheet1.[Gross Revenue]) AS [SumOfGross Revenue]
SELECT Sheet1.[Account Name], Sheet1.[Daily/Sunday]
FROM Sheet1
GROUP BY Sheet1.[Account Name], Sheet1.[Daily/Sunday]
PIVOT Sheet1.Year;

How can I combine the above four queries so that i have one table/query with:

Account Name  Daily/Sunday  Ad Count 08  Ad Count 07  Page Ct 08  Page Ct 07
 Qty 08  Qty 07  Revenue08  Revenue 07  

did you try creating a union query? Not sure it will work with a
crosstab, though...

TRANSFORM...
SELECT DataSet1
FROM
GROUP BY
PIVOT...
UNION ALL
TRANSFORM
SELECT DataSet2...