re-sorting rows to columns in query

[Leathem]

I have a query that produces a table of courses for which a student is registered, but in order to export it for email merge as a csv file it needs to be in a different order. Now each course for which a student is registered gets a different row, using the following SQL:

SELECT StudentEnrollment.EnrollmentID, StudentEnrollment.StudentName, StudentEnrollment.CourseID, StudentEnrollment.Priority, StudentEnrollment.StudentID, CourseData.CourseName, MemberData.[E-MAIL]
FROM MemberData INNER JOIN (CourseData INNER JOIN StudentEnrollment ON CourseData.CourseID = StudentEnrollment.CourseID) ON MemberData.StudentID = StudentEnrollment.StudentID;

However, for the csv file I need a table with a single row for each student listing the courses for which that student is enrolled.

 

[jdraw]

Maybe you need some vba and work with a recordset(s). What did you do when you tested before going to "live" data? Did you think of the output when you were designing?

 

[Leathem]

Actually originally I had designed the system with a VBA procedure to send the emails, a system which works ok with Outlook, but the institution I'm working with now won't use Outlook and prefers something called Maestro for bulk emailing, and Maestro wants a csv file much like mail merge in Word. So yes, I did think of the output earlier, but now they've thrown me a curve and I have to adapt to it.

 

[jdraw]

That's a bummer.
Can you show the table structures in the database and a few records and a sample of the csv format?
I have acc2003 so can NOT use accdb.

 

[Leathem]

Ok, here's the one of the source tables and the query that needs to be modified. As you can see when you run it it produces as many rows for each student (Test0, Test1, etc) as there are courses that student has selected. As I say, I need only one row for each student followed by a series of columns showing which courses that student has selected; for example, "Course1", "Course2", etc as column headings, so for student Test0, for example row 1 would be 12,Test0,AL1,SS2,GS4,SS7,GS7. If we get this far I can include the other data later.View attachment TestDatabase.accdb

 

[jdraw]

I can not open an accdb. Can you post a jpg of your tables and relationships?

Your use of Test1, Test2, ... and Course1, Course2 makes me feel that your structures are not Normalized and that is the reason for query anomalies.

There is info on a database design with students and classes at this site.
http://www.rogersaccesslibrary.com/Tutorials/ClassInformation.zip

 

[Leathem]

First, my apologies for the accdb file - I was in a rush and misread your last reply. I'm attaching the relationships as a pdf along with two Excel files: "enrollments" is the raw data from the registration process and "studentEnrollment" is a processed table that I was using to produce the emails.

 

[jdraw]

No apology needed re the accdb. I went back and edited my post, once I recognized I forgot the word NOT. I edited it after you saw it, I'm sure. I'll look at your materials later.
What does Priority mean?
And DRawR, DRAWS

 

[jdraw]

I have attached a model of your tables.
I also added some data based on your xls files, but I don't see the meaning of Enrollments vs StudentEnrollments. And Priority as well.

Also attached is jpg of query output with Students Enrolled in Courses with Priority.

I have tried Allen Browne's concatrelated to try to get your output but I can not get it to work. I'm not sure what is wrong.

 

[jdraw]

I worked a little more to set up the ConcatRelated function and got this output.

RESULT
1777,test0,AL1, GS4, GS7, SS2, SS7
1778,test1,AL9, LLS2, LLS5, LLS9, LLS13, L2
1779,test2,GS3, LLS9, LLS10, LLS13, SS5, SS6
1780,test3,AL6, LLS8, SS2, SS8, L1, L2
1781,test4,AL6, GS2, LLS10, LLS13, SS5, L1
1782,test5,AL6, GS2, LLS7, LLS10, SS5, L1

And here is the query sql to get the RESULT output (my query ConcatResult)

SELECT [StudentNumber] & "," & [LastName] & "," & ConcatRelated("CourseNumber","QSC","StudentId=" & [studentid]) AS RESULT
FROM LStudent;

and this is QSC sql

SELECT LStudentEnrollment.StudentId
, LCourse.LCourseId
, LCourse.CourseNumber
FROM LCourse INNER JOIN LStudentEnrollment ON
 LCourse.LCourseId = LStudentEnrollment.CourseId;

and the ConCatRelated function is from Allen Browne
available at http://allenbrowne.com/func-concat.html

I exported the query ConcatResult to a csv, but this site won't allow csv uploads. I renamed the file exetension to txt and have uploaded it,
The file is attached as ConcatResult.txt
Take a look. Feedback is encouraged.

 

[jdraw]

Has this been abandoned?