anything is possible
1. create a query something like
Code:
SELECT HeadMemberID, "," & FirstName & "," & Surname & "," & DOB AS ChildDetail, "Child" & DCount("*","Person","HeadMemberID=" & HeadMemberID & " AND PersonID<=" PersonID) AS Childnum
FROM Person
WHERE HeadMemberID<>0
lets call it Children. This lists all your children data - since this will be going to a text file, you might need to change the commas to something else but include them - including the first one - if they need to be in square brackets then modify accordingly. I've also used dcount rather than the faster subquery because this data will be used in a crosstab - and crosstabs and subqueries do not get on.
2. create the next query to add the headmember
Code:
SELECT FirstName & "," & Surname & "," & Email AS HeadMember, ChildDetail
FROM Person LEFT JOIN Children ON Person.PersonID=Children.HeadMemberID
WHERE Person.HeadmemberID=0
Now you can convert this to a crosstab -
HeadMember woud be a row heading
Childnum would be the column heading
ChildDetail would be the Value (use first rather than sum)
you may have to set headings
3. save the crosstab as say allDetail then create a new query which uses alldetail
Code:
SELECT Headmember & nz(Child1) & nz(Child2) & nz(Child3) & nz(Child4) as linedetail
FROM alldetail
You should now see all values as defined, separated by commas on one line and in one field. Note if nulls still need to be represented separated by commas, change
....& nz(Child1) &....
to
....& nz(Child1,",,,") &....