Merging two rows into one

  • Thread starter Thread starter Juliette
  • Start date Start date
J

Juliette

I have a table that has a unique ID for each student, then a unique ID for
each of their parents, which causes two rows to show up for each studen. I
want to create a query where the student's parent information shows in one
row.

Current Data
StudentID ParentID Sex SchoolAttended
123456 8888 M Drexel University
123456 9999 F Arizona State University

What I Would Like
Student ID Parent1ID Parent2ID Parent1Sex Parent2Sex
123456 8888 9999 M F

Any help would be greatly appreciated.
 
Thanks for the link. I actually don't want to concatenate, but move the
unique fields from row 2 into row 1 into their own fields.

Thanks,
Juliette
 
Untested, but something like this might work.

SELECT tblStudent.StudentID, DFirst("ParentID", "tblParent", "StudentID="
& [tblStudent].[StudentID]) AS Parent1ID, DLast("ParentID", "tblParent",
"StudentID=" & [tblStudent].[StudentID]) AS Parent2ID
FROM tblStudent INNER JOIN tblParent ON tblStudent.StudentID =
tblParent.StudentID;

You'll have to adjust for the line wrap and add additional fields using the
same concept for the parent sex.
 
Also, my previous post won't work if the student has more than two
parents listed (step parents, etc.)
 
Back
Top