Flatten a Single Relational Table

frdata · May 7th, 2014, 02:39 PM

Hello authors,

I could not find any vba code in the book to perform the following. Please offer some support.

I have a table StudentResults(StudentName, CourseName, Grade) containing the following sample data :

John, C100, A
John, C101, B+
Betty, C100, A-
Betty, C200, B
Betty, C201, B-
...etc

I need to flatten it so that it becomes as follows :

C100 C101 C200 C201
John A B+ nil nil
Betty A- nil B B-

Hope you could provide vba code to create it

Thank you.

malcolmdixon · May 7th, 2014, 04:41 PM

Hi,

Do you mean SQL to query your table and provide the results in the format you show? You need to create a crosstab query.

Code:

TRANSFORM First(StudentResults.Grade) AS FirstOfGrade
SELECT StudentResults.StudentName
FROM StudentResults
GROUP BY StudentResults.StudentName
PIVOT StudentResults.CourseName;

The above SQL should help.

Malc.

frdata · May 8th, 2014, 04:04 AM

Thats great!

I thought crosstab query can only be used for summary purposes.

This is something useful and more importantly, easy to use.

Thanks so much

malcolmdixon · May 8th, 2014, 05:45 AM

Glad it helped, don't forget to press the Thanks button

Malc.