Transpose columns to rows

[Wendy]

Hi,
I can't remember how to convert columns into rows (I can do it the other way
round).

I have the following data:
ID YEAR PUPIL_ID SUBJECT DESCRIPTION
8348 2009 1207150 AR Art
8524 2009 1207150 DR Drama
8696 2009 1207150 EN English
8793 2009 1207150 FR French
9011 2009 1207150 HI History
9168 2009 1207150 IT Information Technology
9405 2009 1207150 MA Mathematics
9730 2009 1207150 SP Spanish
9821 2009 1207150 GG Geography
10027 2009 1207150 SC Science

 

[Wendy]

Sorry, didn't finish question.

With the data below, I'd like to achieve,

Pupil_ID, Subject1, Subject2, Subject3 etc..

Many thanks

Wendy

 

[John W. Vinson]

Sorry, didn't finish question.

With the data below, I'd like to achieve,

Pupil_ID, Subject1, Subject2, Subject3 etc..

See the online help for "Crosstab" for starters. If that doesn't get what you
want post back with more details about what you'll be doing with this data.
Note that you should emphatically *not* have a table structured as you
describe your output, though a query for presentation purposes should be fine.

 

[Wendy]

Hi,
Crosstab gives me the rows, but by individual subject and leaves me with
blanks throughout the data.

This is for output only...as I need to attach it to another table which
complete the childs whole programme of study.


Wendy

 

[John Spencer]

Here are three sources that have code that allows you to concatenate (combine)
a field from multiple rows into a column. I am assuming that you need this
for a report.

Allen Browne
http://allenbrowne.com/func-concat.html

Duane Hookom
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

The Access Web
http://www.mvps.org/access/modules/mdl0004.htm

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County