cross tab

[subs]

how to join the following two cross tab queries by SQL? pl s help




TRANSFORM Avg([current year].SumOfPAID_AMT) AS AvgOfSumOfPAID_AMT
SELECT [current year].OCITY
FROM [current year]
GROUP BY [current year].OCITY
PIVOT theYear

TRANSFORM Avg([current year].SumOfTRANSPORT_AMT) AS
AvgOfSumOfTRANSPORT_AMT
SELECT [current year].OCITY
FROM [current year]
GROUP BY [current year].OCITY
PIVOT theYear;

 

[Jerry Whittle]

Create a query like below:

SELECT [current year].OCITY ,
"PAID_AMT" as Amounts,
Avg([current year].SumOfPAID_AMT) AS AvgOfSumOfPAID_AMT
FROM [current year]
GROUP BY [current year].OCITY, AmountType
UNION ALL
SELECT [current year].OCITY ,
"PAID_AMT" As AmountType,
Avg([current year].SumOfTRANSPORT_AMT) AS AvgOfSumOfTRANSPORT_AMT
FROM [current year]
GROUP BY [current year].OCITY, AmountType;

Then use the above as the basis for a crosstab query. You may not need the
AmountType lines.