Concatenate multiple rows

[Guest]

Is there a good way to concatenate data from multiple rows into one field? I
have several rows with different dates and I need to combine the comment text
for each of the dates into one field and group it all as one record. How can
I do that?

 

[Duane Hookom]

There is a generic function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[Guest]

Mine was a bit more complicated than that, but thanks to your code and
example I finally got it figured out.

 

[jenniferspnc]

I used the concatenate function succesfully once; however, I applied the same
concept to another query and it's not working properly.

Here's an example of my tables related to what I'm doing:
Countries (Table)
Country_ID (PK)
Country

Currencies (Table)
Currency_ID (PK)
Currency

tbl_currencybridge (Table)
UniqueID (PK)
Currency_ID
Country_ID

So for my first query (currencytest) I have: SELECT
tbl_currencybridge.Country_ID, Currencies.Currency
FROM Currencies INNER JOIN tbl_currencybridge ON Currencies.Currency_ID =
tbl_currencybridge.Currency_ID;

And then ran the second query (currency concatenate)
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT currency
FROM currencytest WHERE Country_ID =" & [Country_ID]) AS Currency_Offered
FROM Countries;
The above works perfectly.

Now trying to apply the same logic to languages and keep getting a runtime
error.
Languages (Table)
Language_ID (PK)
Language

tbl_Languagebridge (Table)
LanguageUniqueID (PK)
Language_ID
Country_ID

first query (languagetest)
SELECT tbl_languagebridge.Country_ID, Languages.Language
FROM Languages INNER JOIN tbl_languagebridge ON Languages.Language_ID =
tbl_languagebridge.Language_ID;

second query works; however, it shows me the language ID instead of the
Languages.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered
FROM Countries;

So when I try to substitue the language field
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT language
FROM languagetest WHERE Country_ID =" & [Country_ID]) AS languages_Offered
FROM Countries;

I get the error Method 'Open' of object '_Recordset' failed

Help is greatly appreciated. Thank you!!

 

[Sherri Dean]

@Duane Hookom
I have a similar problem as above, but this link no longer works. Any
current advice?

 

[Duane Hookom]

I believe this issue has been handled off-line