TRANSFORM Count(MORIS_CONTRACT_MONTHLY.ACCOUNT_NUM) AS [The Value]
SELECT MORIS_CONTRACT_MONTHLY.REMAINING_TERM, Count(MORIS_CONTRACT_MONTHLY.ACCOUNT_NUM) AS [Total Of ACCOUNT_NUM]
FROM MORIS_CONTRACT_MONTHLY INNER JOIN Program ON MORIS_CONTRACT_MONTHLY.FINANCE_PGM_CD = Program.PROGRAMCD
WHERE (((MORIS_CONTRACT_MONTHLY.ACTIVE_CONTRACT_FLG)="Y") AND ((MORIS_CONTRACT_MONTHLY.CONTRACT_DIV_CD)="FASTRUCK"))
GROUP BY MORIS_CONTRACT_MONTHLY.REMAINING_TERM
PIVOT Mid([Program]![Program],1,3);
that is the cross tab query!!!! can anyone suggest on how to go about it!!!
Do you want to do this properly (use framework manager etc.) or do you just want to try and convert this to pass through SQL?
Will your datasource be MS Access?
An important thing to note is that in Cognos, you would perform the crosstab in the presentation part. Your query would return a standard table with Mid([Program]![Program],1,3) as a SINGLE column with multiple values.
Then you would drop the query on to a crosstab object in query studio.
Thank you!!!for the input!!!!!!
it really worked!!!!!