Solved Concatenating into nested records

[niawo]

Hi. I'm trying to concatenate records from different fields in a query, and found this old database by Duane Hookom, which I've adapted slightly. However, I'm trying to get it to nest the concatenated results. e.g. in queNames I'm trying to get Christina (Associate, Neighbor, Partner), rather than listed separately, but can't work out how to do that. Is there a straightforward way to do it?

Thanks!

 

[theDBguy]

Hi. Have you tried this one?

Code Snippets

 

[niawo]

Hi. Thanks for this. However, it seems it will only list Christina (Associate), Christina (Neighbor), Christina (Partner), rather than nesting them as above. Or am I doing something wrong?

 

[Josef P.]

Not necessarily pretty, but works with the existing function:

SELECT
   tblFamily.FamID, tblFamily.famLastName,
   Concatenate("SELECT distinct FirstName & ' (' &
                     Concatenate(""select Role from tblFamMem where FamID="" & [FamID] & "" and  FirstName ='"" & FirstName & ""'"") & ')'
              FROM tblFamMem WHERE FamID =" & [FamID]) AS FirstNames
FROM tblFamily;

BTW: It can also happen that father and son have the same first name. => Simon (Dad, Son)

 

[cheekybuddha]

The real problem is in the table design.

tblFamMem should not contain the role. Role should be linked to a family member through a M:M link (junction table and Roles table)

In your tables as you have it Christina is four different people.

 

[June7]

Have to use FamID along with FirstName as member identifier. Unless kids have same name ala George Foreman, also as Josef stated, even that combination won't handle same name in multiple generations.

 

[niawo]

Not necessarily pretty, but works with the existing function:

SELECT
   tblFamily.FamID, tblFamily.famLastName,
   Concatenate("SELECT distinct FirstName & ' (' &
                     Concatenate(""select Role from tblFamMem where FamID="" & [FamID] & "" and  FirstName ='"" & FirstName & ""'"") & ')'
              FROM tblFamMem WHERE FamID =" & [FamID]) AS FirstNames
FROM tblFamily;

BTW: It can also happen that father and son have the same first name. => Simon (Dad, Son)

Thanks! This works perfectly!

 

[niawo]

Not necessarily pretty, but works with the existing function:

SELECT
   tblFamily.FamID, tblFamily.famLastName,
   Concatenate("SELECT distinct FirstName & ' (' &
                     Concatenate(""select Role from tblFamMem where FamID="" & [FamID] & "" and  FirstName ='"" & FirstName & ""'"") & ')'
              FROM tblFamMem WHERE FamID =" & [FamID]) AS FirstNames
FROM tblFamily;

BTW: It can also happen that father and son have the same first name. => Simon (Dad, Son)

Sorry, do you know if it's possible to add ORDER BY to this?

 

[June7]

Try it. What happens?

 

[niawo]

Try it. What happens?

I'm getting ORDER BY conflicts with DISTINCT

 

[Josef P.]

Try

Order by 1

 

[cheekybuddha]

What are you trying to ORDER ?

By FirstName, or by Role within the concatenated list?

 

[niawo]

What are you trying to ORDER ?

By FirstName, or by Role within the concatenated list?

Neither. It's by an additional field in tblFamMem, which applies a custom sorting to the list

 

[Josef P.]

It's by an additional field in tblFamMem

Which one?

Distinct + Order by: the data field must be present in the select part.
You could also use a group-by statement instead of distinct.

 

[niawo]

Which one?

Distinct + Order by: the data field must be present in the select part.
You could also use a group-by statement instead of distinct.

Thanks. It's the Order field here in tblFamMem

 

[Josef P.]

Is that what you mean?

SELECT
   FamID, famLastName
   , Concatenate("
                 SELECT distinct
                             FirstName & ' (' & Concatenate(
                                   ""select Role from tblFamMem where FamID="" & [FamID] & "" and  FirstName ='"" & FirstName & ""'"") & ')'
                             , [Order]
                   FROM
                          tblFamMem WHERE FamID =" & [FamID] & " order by [Order]"
     ) AS FirstNames
FROM
    tblFamily

BTW: It would be nice to get a sample file that you can open without errors. Shows the value a little bit for me.

 

[niawo]

Is that what you mean?

SELECT
   FamID, famLastName
   , Concatenate("
                 SELECT distinct
                             FirstName & ' (' & Concatenate(
                                   ""select Role from tblFamMem where FamID="" & [FamID] & "" and  FirstName ='"" & FirstName & ""'"") & ')'
                             , [Order]
                   FROM
                          tblFamMem WHERE FamID =" & [FamID] & " order by [Order]"
     ) AS FirstNames
FROM
    tblFamily

BTW: It would be nice to get a sample file that you can open without errors. Shows the value a little bit for me.

Yes, that works great. Many thanks! And apologies for the errors.