How to concatenate child fields?

W

WDSnews

I have a people table containing parents and children. There is a
"Relatives" table defining the relationship between people in the people
table. The goal is to create a query that produces one record per name in
the People table, and a field containing the concatenated first names of all
the relatives. How can I concatenate child fields?

|People| |Relatives| |People|
ID|--->> PeopleID|
RelativeID|---> ID|
 
A

Allen Browne

Is easiest to do this with a VBA function.

Details in:
Concatenate values from related records
at:
http://allenbrowne.com/func-concat.html

The query will end up something like this:
SELECT ID,
ConcatRelated("[FirstName] & "" & "" & [Surname] AS FullName",
"tblRelatives", "RelativeID = " & [ID]) AS Relatives
FROM Table1.
 
A

aaron.kempf

SQL Server supports CTE which makes things like this easier.

if you're really using parent-child relationships, you should be on a
database engine with a future--
jet has been obsolete for a decade
 
W

WDSnews

thank you.


Allen Browne said:
Is easiest to do this with a VBA function.

Details in:
Concatenate values from related records
at:
http://allenbrowne.com/func-concat.html

The query will end up something like this:
SELECT ID,
ConcatRelated("[FirstName] & "" & "" & [Surname] AS FullName",
"tblRelatives", "RelativeID = " & [ID]) AS Relatives
FROM Table1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

WDSnews said:
I have a people table containing parents and children. There is a
"Relatives" table defining the relationship between people in the people
table. The goal is to create a query that produces one record per name in
the People table, and a field containing the concatenated first names of
all the relatives. How can I concatenate child fields?

|People| |Relatives| |People|
ID|--->> PeopleID|
RelativeID|---> ID|
 
W

WDSnews

thank you.


SQL Server supports CTE which makes things like this easier.

if you're really using parent-child relationships, you should be on a
database engine with a future--
jet has been obsolete for a decade
 

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