Creating a query with one to many all on one line

[Jay Oken]

I have two tables, Parent and Children, with a one-to-many relationship. I
would like to create a query that creates a single record per
parent/children listing the parent and then as many children as they have
listed all in the same record. I assume that I would have to do this with a
SQL query?

 

[Ivan Grozney]

Jay,

Check out
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Generic Function To Concatenate Child Records (46 KB)

Works great and shows you different ways to use it.

hth

Vanya

 

[Jay Oken]

Thanks Ivan. Any ideas on how to get the concatenated data into separate
fields so that I can export the query to an excel spreadsheet.

 

[John Spencer]

Try using a tab character Chr(9) as the second argument to the Concat
function. Of course, that will probably end up showing you a square in
the query, but it will probably put the data into separate cells when
you export to Excel.

If that doesn't work then you could write a ranking query to rank each
child record and then a crosstab query to return the data in multiple fields

SELECT Parent.*
, Children.*
, (SELECT Count(*)
FROM Children as C
WHERE C.ParentID = Parent.ParentID
AND C.ChildID < Children.ChildID) as Rank
FROM Parent INNER JOIN Children
ON Parent.ParentID = Children.ParentID

Use that in a crosstab query, that would look something like the following.

TRANSFORM First(Child)
SELECT Parent.ParentID
FROM qRankedChildren
GROUP BY Parent.ParentID
PIVOT Rank

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================