Print multiple records in one row using Access

I'm rather stumped as it's been several years since I've worked with databases. It's usually the easiest stuff that hurts and I'm hoping someone can help.

I have a table with student names. I have a table with parent names (both mother and father). I have another table that defines their relationship as mother and father, in addition to another table that defines that keeps track of all the primary keys and their relationship. I've reached the point where I remembered how to join, but can only join one record at a time. Currently I have a query in Access 2010 that prints two rows as follows:

Student1 Name | Parent1 Name | Parent1 Cell  
Student1 Name | Parent2 Name | Parent2 Cell
Student2 Name | Parent1 Name | Parent1 Cell  
etc...

I would like to accomplish the following:

Student1 Name | Parent1 Name | Parent2 Name | Parent1 Cell | Parent2 Cell  
Student2 Name | Parent1 Name | Parent2 Name | Parent1 Cell | Parent2 Cell  
etc...

My SQL statement looks like so:

SELECT Student.FirstName, StudentToPersonMap.RelationshipType, Person.FirstName, 
Person.CellPhone, FROM Student INNER JOIN (Person INNER JOIN StudentToPersonMap ON 
Person.PersonID = StudentToPersonMap.PersonID) ON Student.StudentID = 
StudentToPersonMap.StudentID WHERE (((StudentToPersonMap.RelationshipType)=1 Or 
(StudentToPersonMap.RelationshipType)=2));

Any suggestion ei开发者_JAVA技巧ther through an Access query or through a SQL query would be most welcome! Thank you for reading!

You could use sub quires. Here is an example from another system based on an SQL server but you can use the same theory in your application

SELECT tblProcedures.Procedure_ID,
        Procedure_name,
        tReviewer_1.Proc_reviewer_name,
        tReviewer_2.Proc_reviewer_name
FROM tblProcedures
INNER JOIN (select Procedure_ID, Proc_reviewer_name 
                from tblProc_reviewers 
                    INNER JOIN tblProc_reviews_req 
                        ON tblProc_reviews_req.Proc_reviewer_ID=tblProc_reviewers.Proc_reviewer_ID
                    WHERE tblProc_reviewers.Proc_reviewer_ID=1) as tReviewer_1
ON tReviewer_1.Procedure_ID=tblProcedures.Procedure_ID

INNER JOIN (select Procedure_ID, Proc_reviewer_name 
                from tblProc_reviewers 
                    INNER JOIN tblProc_reviews_req 
                        ON tblProc_reviews_req.Proc_reviewer_ID=tblProc_reviewers.Proc_reviewer_ID
                    WHERE tblProc_reviewers.Proc_reviewer_ID=2) as tReviewer_2
ON tReviewer_1.Procedure_ID=tblProcedures.Procedure_ID

In access you might be better off making two queries one called qryStudent_mothers_details and one called qryStudent_fathers_details.

You could then join these to your students table and go from there.