Column to Rows

[Mohabi]

Hi

I have a table like this:

Type Name Month
A PersonA Jan-10
A PersonA May-10
A PersonA Jun-10
A PersonB Apr-10
A PersonC Apr-10
B PersonD Jan-10
B PersonD Feb-10
B PersonA Mar-10
B PersonA Apr-10
B PersonB May-10
B PersonB Jun-10
B PersonC May-10
B PersonC Jun-10
B PersonE Jan-10
C PersonF Mar-10
D PersonG Jan-10
D PersonG Feb-10
D PersonG Mar-10
D PersonG Apr-10
D PersonG May-10
D PersonG Jun-10
D PersonH Jan-10
D PersonH Feb-10
D PersonH Mar-10
D PersonH Apr-10
D PersonH May-10
D PersonH Jun-10
D PersonI Jan-10
D PersonI Feb-10
D PersonI Mar-10
D PersonI Apr-10
D PersonI May-10
D PersonI Jun-10
E Personj Jan-10
E Personj Feb-10
E Personj Mar-10
E Personj Apr-10
E Personj May-10
E Personj Jun-10

Want to convert into a table like this:
Type Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10
A PersonA PersonB PersonA PersonA
A PersonC
B PersonD PersonD PersonA PersonA PersonB PersonB
B PersonE PersonC PersonC
C PersonF
D PersonG PersonG PersonG PersonG PersonG PersonG
D PersonH PersonH PersonH PersonH PersonH PersonH
D Personi Personi Personi Personi Personi Personi
E Personj Personj Personj Personj Personj Personj

I tried Cross tab query - but this gives me the result for each name
as a separate row if I use Group by.
I cannot use pivot feature of Access 2003 because I cannot edit it.
If you have any idea would be highly appreciated.

Thanks
bmohabi

 

[MGFoster]

Hi

I have a table like this:

Type Name Month
A PersonA Jan-10
A PersonA May-10
A PersonA Jun-10
A PersonB Apr-10
A PersonC Apr-10
B PersonD Jan-10
B PersonD Feb-10
B PersonA Mar-10
B PersonA Apr-10
B PersonB May-10
B PersonB Jun-10
B PersonC May-10
B PersonC Jun-10
B PersonE Jan-10
C PersonF Mar-10
D PersonG Jan-10
D PersonG Feb-10
D PersonG Mar-10
D PersonG Apr-10
D PersonG May-10
D PersonG Jun-10
D PersonH Jan-10
D PersonH Feb-10
D PersonH Mar-10
D PersonH Apr-10
D PersonH May-10
D PersonH Jun-10
D PersonI Jan-10
D PersonI Feb-10
D PersonI Mar-10
D PersonI Apr-10
D PersonI May-10
D PersonI Jun-10
E Personj Jan-10
E Personj Feb-10
E Personj Mar-10
E Personj Apr-10
E Personj May-10
E Personj Jun-10

Want to convert into a table like this:
Type Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10
A PersonA PersonB PersonA PersonA
A PersonC
B PersonD PersonD PersonA PersonA PersonB PersonB
B PersonE PersonC PersonC
C PersonF
D PersonG PersonG PersonG PersonG PersonG PersonG
D PersonH PersonH PersonH PersonH PersonH PersonH
D Personi Personi Personi Personi Personi Personi
E Personj Personj Personj Personj Personj Personj

I tried Cross tab query - but this gives me the result for each name
as a separate row if I use Group by.
I cannot use pivot feature of Access 2003 because I cannot edit it.
If you have any idea would be highly appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Like this:

TRANSFORM FIRST([name]) As theValue
SELECT [type]
FROM table_name
GROUP BY [type]
PIVOT [Month]

I put square brackets around the column names 'cuz they all are reserved
words in either Access or SQL, or both! Try changing them to a more
descriptive phrase. E.g.: worker_name, payment_type, pay_month.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSt6KXYechKqOuFEgEQIKpgCg9lEglWMsmmR2ypFQhzqSWB0beMgAoIUr
orXhKRQ++mkAG62lWCJ8iLAM
=dzbA
-----END PGP SIGNATURE-----