Vfrdtyky

Using cross tab query in MS Access SQL
how to transform table like this

===========================

|IND     |LBL     |TICKETS|

===========================

|1       |AM      |101    |

|2       |AM      |1      |

|1       |HR      |102    |

|1       |TV      |100    |

|2       |TV      |2      |

===========================

to this

=======================

|LBL     |IND1   |IND2|

=======================

|AM      |101    |1   |

|HR      |102    |0   |

|TV      |100    |2   |

=======================

this is the query that i tried.

I think I have made some mistake in the crosstab query, if you could point out, it would be helpful

TRANSFORM First(TBL1.TICKETS) AS TCK

SELECT  TBL1.LBL, First(TBL1.TICKETS) AS TCK1

from 

(

SELECT IIF(A.AM= '[AM]' OR A.AM='[AM2]',1,2) AS IND

          , 'AM' AS LBL

          , COUNT(A.AM) AS TICKETS 

  FROM shree_main AS A GROUP BY IIF(A.AM='[AM]' OR A.AM='[AM2]',1,2), 'AM'



union all



SELECT IIF(A.Newpri= '[HR]' OR A.Newpri='[NML]' OR A.Newpri='[N2H]',1 ,2) AS IND, 'HR' AS LBL,

   count([A].Newpri) AS TICKETS FROM shree_main AS A 

    GROUP BY IIF(A.Newpri= '[HR]' OR A.Newpri='[NML]' OR A.Newpri='[N2H]',1,2), 'HR'



union all 

SELECT IIF(A.TV= '[SC]' OR A.TV='[TV]' OR A.TV='[TV][SC]' OR A.TV='[VE]',1 ,2) AS IND, 'TV' AS LBL, COUNT(A.TV) AS TICKETS FROM shree_main AS A GROUP BY IIF(A.TV= '[SC]' OR A.TV='[TV]' OR A.TV='[TV][SC]' OR A.TV='[VE]',1 ,2), 'TV'



)   AS TBL1



GROUP BY  TBL1.LBL, TBL1.LBL, TBL1.LBL, TBL1.LBL

PIVOT TBL1.LBL;

and this is the result of the query

LBL TCK1    AM    HR     TV

AM  1       101     

HR  102           102   

TV  2                   100