Creating a query with one to many all on one line

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?

Jay,

Check out
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

Generic Function To Concatenate Child Records (46 KB)

Works great and shows you different ways to use it.

hth

Vanya





"Jay Oken" wrote:

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?

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.


"Ivan Grozney" stormovick (ditch) @hotmail.com wrote in message
...
Jay,

Check out
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

Generic Function To Concatenate Child Records (46 KB)

Works great and shows you different ways to use it.

hth

Vanya





"Jay Oken" wrote:

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?

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


Jay Oken wrote:
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.


"Ivan Grozney" stormovick (ditch) @hotmail.com wrote in message
...
Jay,

Check out
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

Generic Function To Concatenate Child Records (46 KB)

Works great and shows you different ways to use it.

hth

Vanya





"Jay Oken" wrote:

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?