Record1 to Column1, Record2 to Column2? (1 Viewer)

[MyTech]

Hi all,

I have a table of students and their parents information.

Example:
ChildFirstName,_ ParentID,_ ParentFirstName,_ ParentLastName.
Child1___________ 1_________ ParentF1_________ ParentL1
Child2___________ 2_________ ParentF2_________ ParentL2
Child3___________ 3_________ ParentF3_________ ParentL3
Child4___________ 2_________ ParentF2_________ ParentL2

Child 2 and 4 have the same parent.

How can I in a Query (Group By [ParentID]) do the following:
add every first found child to 'Column5',
add every Second found child to 'Column6'

So that 'Parent2' will have 'Child2' in 'Column5', and 'Child4' in 'column6'.


Any suggestion?

 

[MyTech]

The following SQL gives me the FIRST found child in one column, and the LAST found child in another column.

SELECT
Children.ID,
Count(Children.ID) AS [Count],
Children.Father,
Children.Family,
First(Children.Child) AS [First],
IIf(First([child])<>Last([Child]),Last([Child]),"") AS [Last]
FROM Children
GROUP BY Children.ID, Children.Father, Children.Family;

What do I do if a parent has more than 2 children???

 

[Galaxiom]

First and Last will enter the a single child twice.

The solution is quite a complex query. Basically you start with a self join on the table using the FamilyID. Just drag the table into the designer twice. And join on the FamilyID.

This query generates a record for each child in the family times the number of children in the family, one for each sibling and themselves.

Use this as the base for another query and derive a field that counts the number of children in the child group that have a child name in the second copy of the table less than the child record being processed. (Less than will use alpha order).

This number will be used to get the column number for your final output using IIF statements in yet another query.