2 crosstabs

[geebee]

hi,

I have the following 2 queries:

calls_by_status:
TRANSFORM Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode.[COUNT( *
)]) AS [SumOfCOUNT( * )]
SELECT tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode
GROUP BY tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus
PIVOT tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt;

calls_by_status_and_code:
TRANSFORM Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode.[COUNT( *
)]) AS [SumOfCOUNT( * )]
SELECT tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.[party code],
tbl_CACS_activity_by_resolvedstatusandcontactcode.[activity cd]
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode
GROUP BY tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.[party code],
tbl_CACS_activity_by_resolvedstatusandcontactcode.[activity cd]
PIVOT tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt;

I have no problem using one query as the source for a line graph. The
problems comes when I want to combine both queries into one line graph. I
have seen a post suggesting the following SQL, but I cant seem to make any
sense of it to generate my own SQL:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order Details].OrderID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);


Does anyone have something I can work with?

Thanks in advance,
geebee

 

[Duane Hookom]

Looks like a solution I posted. I don't think it will work with your two
crosstabs since your Row Headings are not the same.