How to Join two tables and avoid multiple rows, and get repeating columns instead!

[JH]

Folks,



I have a large database that, amongst many other tables has one holding
details about mothers (tblMothers) and one holding details of their children
(tblChildren). I am trying to write a query, or series of queries that will
produce one row per mother with details of her children repeated in the same
row.



For example:



Assume tblMothers has three records in it, and that there are only two items
per row, MotherId and MotherName



MotherID MotherName

1 Mary

2 Jane

3 Elizabeth



Asuume tblChildren has three columns, MotherID, ChilrenDateOfBirth,
ChildrenName



MotherID ChildrenDateOfBirth ChildrenName

1 01/01/2000 Jo

1 01/01/2001 Mike

1 01/01/2002 Sally

1 01/01/2003 James

2 02/02/2002 Nathan

2 02/02/2002 Tom

2 Peter



Note that MotherID 2 has twins, ie the ChildrenDateOfBirth is the same, and
has a third child but the date of birth is not known, and that MotherID 3
has no children.



What I need as output from the query is:



MotherID MotherName Child1DoB Child2DoB Child3DoB
Child4DoB

1 Mary 01/01/2000 01/01/2001
01/01/2002 01/01/2003

2 Jane 02/02/2002 02/02/2002
Unknown

3 Elizabeth



Clearly the Child1DoB etc has to expand to accommodate the mother with the
most children. The real database has many thousands of entries in both
tables.



I nearly solved it using a primary query that numbered children sequentially
and then a crosstab query to produce the final table, but it does not
distinguish children with the same date of birth.



Any suggestions or solutions will be gratefully receicved!



John

 

[JH]

Sorry my formatting seems to been screwed! The output line MotherID,
MotherName, Child1DOB, Child2DoB, etc should alll be on one line.

John

 

[Van T. Dinh]

Check Access Help on Cross-tab Queries.