Multiple Rows -> Columns

[mabiem]

I have a Table/Query that looks like:

Student ID AP Course Grade
112233 ENGL A
112233 FREN B
456789 HIST C
456789 ENGL A
345432 SPAN C

I need it to look like:
Student ID AP Course1 Grade1 AP Course2 Grade2
112233 ENGL A FREN B
456789 HIST C ENGL A
345432 SPAN C


And so on....there might be as many as 15 courses.

I no how to go the other way with a UnionALL query, but how to I go this way?

Mike

 

[CJ_London]

It is doable - there are a number of threads on this forum which require something similar.

This link should help - you'll need to adapt the function for your particulat requirements - i.e. requiring concatenation of two fields from each record

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

 

[Pat Hartman]

A crosstab query may also solve the problem and it doesn't require any code.

 

[mabiem]

Back from a long weekend. Thanks for the leads guys. I may start with the cross-tab and see if I can get where I need with that. If not I'll look at the Allen Browne link.

 

[CJ_London]

A cross tab will only give you one column value, whereas you require two (Course - ENGL, Grade - 2) per your original post

However you can get it to display as follows

Student    ENGL    FREN    HIST
112233       A        B
456789       A               C

Per your original post, providing you have a course number for your headings - Course1, Course2 etc in your table then you can concatenate course name and grade to provide a single value to use in a crosstab.

 

[mabiem]

Still having some issues. I'll just explain what I'm trying to do. I feel like I missing something obvious.

I received raw data about AP Test Scores from Students. It comes is a raw CSV feed that I import into Access. The original CVS come is with one row per ID and a column for the 30 possible tests and test scores:

ID Test1 Score1 Test2 Score2....
12345 Hist 1 ENGL 5

I present that in a form for the evaluators with a record for each student. So I create a Union query that looks like:

ID Test Score Equivalency Transcript Rec'd
12345 HIST 1
12345 ENGL 5

WHich I then turn into a make table so the evaluators can enter the extra info in a form and the table ends up looking like:

ID Test Score Equivalency Transcript Rec'd
12345 HIST 1 HIST 100 Yes
12345 ENGL 5 ENGL 100 Yes


Now I need to run a query where I take this info and some other info and create a document for each students that summarizes the status of the credits they reported. To run this letter merge. I can do this but I need to revery back to the original format with the new info added. Like this:

ID Test1 Score1 Equivalency1 Transcript1 Test2 Score2 Equivalency2 Transcript2
12345 HIST 1 HIST 100 Yes ENGL 5 ENGL 100 Yes

And so on.

I was able to get to this:
ID Test Score Equivalency Transcript Rec'd
12345 HIST,ENGL 1,5 HIST 100,ENGL 100 Yes,Yes


Which might be getting closer....

 

[mabiem]

For some reason I can't run VBA on my work computer so I exported to excel and ran this macro to get what I have now.

Sub JoinThem()
' Go until blank cell (first click at upper left of range)
Do While ActiveCell.Value <> ""
' If this row macthes the next row
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
' Join first column overwriting the values
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1).Value & ", " & ActiveCell.Offset(1, 1).Value
' and Join the second column too
ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 2).Value & ", " & ActiveCell.Offset(1, 2).Value
' and Join the third column too
ActiveCell.Offset(0, 3) = ActiveCell.Offset(0, 3).Value & ", " & ActiveCell.Offset(1, 3).Value
' and Join the four column too
ActiveCell.Offset(0, 4) = ActiveCell.Offset(0, 4).Value & ", " & ActiveCell.Offset(1, 4).Value
' Since we placed the data into one row get rid of the row
ActiveCell.Offset(1, 0).EntireRow.Delete
Else
' Next row does not match, move down one
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub