Creating a query with one to many all on one line

J

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?
 
J

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.
 
J

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
'====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top