If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Concatenate multiple rows
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? -- THX cs |
#2
|
|||
|
|||
Concatenate multiple rows
There is a generic function with sample usage at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane -- Duane Hookom MS Access MVP -- "Cydney" wrote in message ... 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? -- THX cs |
#3
|
|||
|
|||
Concatenate multiple rows
Mine was a bit more complicated than that, but thanks to your code and
example I finally got it figured out. -- THX cs "Duane Hookom" wrote: There is a generic function with sample usage at http://www.rogersaccesslibrary.com/O...p#Hookom,Duane -- Duane Hookom MS Access MVP -- "Cydney" wrote in message ... 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? -- THX cs |
#4
|
|||
|
|||
Concatenate multiple rows
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!! "Duane Hookom" wrote: There is a generic function with sample usage at http://www.rogersaccesslibrary.com/O...p#Hookom,Duane -- Duane Hookom MS Access MVP -- "Cydney" wrote in message ... 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? -- THX cs |
#5
|
|||
|
|||
Concatenate multiple rows
@Duane Hookom
I have a similar problem as above, but this link no longer works. Any current advice? "Duane Hookom" wrote: There is a generic function with sample usage at http://www.rogersaccesslibrary.com/O...p#Hookom,Duane -- Duane Hookom MS Access MVP -- "Cydney" wrote in message ... 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? -- THX cs |
#6
|
|||
|
|||
Concatenate multiple rows
I believe this issue has been handled off-line :-)
-- Duane Hookom MS Access MVP "Sherri Dean" wrote in message ... @Duane Hookom I have a similar problem as above, but this link no longer works. Any current advice? "Duane Hookom" wrote: There is a generic function with sample usage at http://www.rogersaccesslibrary.com/O...p#Hookom,Duane -- Duane Hookom MS Access MVP -- "Cydney" wrote in message ... 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? -- THX cs |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
generate multiple rows based on cell value | Theresa | Worksheet Functions | 0 | May 25th, 2005 11:18 PM |
Insert rows | Mr. G. | Worksheet Functions | 3 | March 31st, 2005 03:49 AM |
How can I convert a data from multiple rows into 1 column? | yudi_lks | Worksheet Functions | 10 | January 30th, 2005 03:47 AM |
How do I add multiple rows to a table? | Adding rows to tables | Tables | 3 | January 29th, 2005 08:26 PM |
Merging multiple rows into one with multiple columns | Zattack | Running & Setting Up Queries | 3 | August 30th, 2004 10:23 PM |