Merging two rows into one

[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.

 

[Beetle]

Have a look at Duane Hookoms example application at this link;

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

 

[Juliette]

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

 

[Beetle]

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.

 

[Beetle]

Also, my previous post won't work if the student has more than two
parents listed (step parents, etc.)

 

[Juliette]

Thank you so much, Sean. This helped me figure out how to do the query.

Juliette