Transforming vertical data to horizontal data

W

wooster

Hi,
Wondering if anyone can help me? I have a series of data that looks
like:

Pupil_ID Code Subject Teacher
980656 BEL004 Information Technology BARR, Iain
980656 BEL004 Biology FRENCH, Julia
980656 BEL004 Business Vocational RIMMINGTON, Mark
980656 BEL004 Business Vocational JACKSON, Barry
980656 BEL004 Home Economics AICKEN, Mary
980656 BEL004 Physical Education WELLS, Alan
980656 BEL004 Careers WARD, Graham
980656 BEL004 English AGABA, Faith
980656 BEL004 Mathematics KRAUSE, Michael
980897 BRI001 Art GRAHAM, David

Each child will have a varying number of records dependent upon the
number of subjects they study. For each student I need to display the
data as a single record.

Pupil_ID, Code, Subject1, Teacher 1, Subject2, Teacher2 etc.

I can't seem to use the Transform option as I'm not aggregating any
data.

I'm having to use the data for a mail merge and I need all the data for
each student accessible in one record. If anyone has any other
suggestions I'd appreciate them as I've got so blinkered on this issue
now.

Thanks

Woo
 
J

Jason Lepack

Do you need this information in Access or in an Excel Spreadsheet?

Cheers,
Jason Lepack
 
W

wooster

Either will do. If it's Excel I know I have to do some form of analysis
of each row in the selected range, if the pupil id is the same as the
previous then offset x,y.select, cut offset y,x and paste - do until
the range.value <> - I'm stuck on the loopy bit though.

Thanks

Woo
 
J

Jason Lepack

Public Sub flatFile()
On Error GoTo flatFile_Err
Dim wb As Workbook
Dim wsOld As Worksheet, wsNew As Worksheet
Dim rOld As Range, rNew As Range
Dim i As Integer

Set wb = ActiveWorkbook
Set wsOld = wb.ActiveSheet
Set wsNew = wb.Sheets.Add

wsOld.Range("1:1").Copy
wsNew.Range("A1").PasteSpecial xlPasteAll

wsOld.Cells.Sort key1:=wsOld.Range("A2"), _
key2:=wsOld.Range("C2"), Header:=xlYes

Set rOld = wsOld.Range("A2")
Set rNew = wsNew.Range("A1")

Do While Not rOld.Value = ""
If Not rOld.Value = rNew.Value Then
Set rNew = rNew.Offset(1, 0)
rNew.Value = rOld.Value
rNew.Offset(0, 1).Value = rOld.Offset(0, 1).Value
i = 2
End If
rNew.Offset(0, i).Value = rOld.Offset(0, 2)
rNew.Offset(0, i + 1).Value = rOld.Offset(0, 3)
i = i + 2
Set rOld = rOld.Offset(1, 0)
Loop
flatFile_Goodbye:
Set rOld = Nothing
Set rNew = Nothing
Set wsOld = Nothing
Set wsNew = Nothing
Set wb = Nothing
Exit Sub
flatFile_Err:
MsgBox Err.Number & " - " & Err.Description
Resume flatFile_Goodbye
Resume Next
End Sub
 
J

John Spencer

You might be best off using Duane Hookom's concatenate function to do this.

SELECT Pupil_ID
, Code
, Concatenate ("SELECT Subject & ', ' & Teacher FROM [TheTable] WHERE
Pupil_ID = " & Pupil_ID & " AND Code ='" & Code & "' ", " : ") as Listing
FROM [TheTable]

Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with both
ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO while
the default for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
W

wooster

Great! Thanks for your help. Haven't tried it yet, just too busy...but
now you've given me the bones I can extend it. Thanks again.
 
Top