Concatenate fields in related tables

[Freshman]

Hi all,

I need some help with the following please:

I have 2 related tables (on Field1) and I want a query to display the "concatenated" data without using a VBA function.

Thanks a lot!

 

[plog]

...without using a VBA function.

Not possible. There is a prebuilt VBA function however you can paste into a module and use:

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

 

[cheekybuddha]

See here on DBguy's site for a useful function that can help you achieve this

 

[cheekybuddha]

without using a VBA function.

Oops! missed that bit!

 

[Freshman]

Thanks - yes I can do it with a function but it's not me that needs it this time. I need to give the SQL to another developer not using Access. I'm waiting to hear that language he is using but thought if I could give him something in pure SQL it would be eaier for him.
Anyway - will wait for him and then he must find a function in the platform he is using.

Thank again!

 

[cheekybuddha]

If he is using a RDBMS other than Access, then it is likely that it will have a built-in equivalent function (eg GROUP_CONCAT() in MySQL)

What is the backend he is using?

 

[Freshman]

Thanks - will ask him about his backend as well. Not sure. They are a 3rd party who is supposed to work with us but keep thing close to their chest while still not delivering. Yes I know

 

[theDBguy]

In T-SQL, I've used the FOR XML PATH clause.

 

[cheekybuddha]

T-SQL now has the STRING_AGG() function which is much simpler!

(See here)

 

[theDBguy]

T-SQL now has the STRING_AGG() function which is much simpler!

(See here)

Cool. Thanks!

PS. I just need to remember to use it for 2017 and up.

 

[Freshman]

Thanks - I will add it to the notes