Concatenate Column Values from Multiple Rows into a Single Column

[erncelen]

Hi to everybody,
I need another help,

I created a query with several field, when the query run, the result is a nuber of row for the same "criteria",
e.g.

ID, Field1, Field2,...
1 , x , y,...
1, z , m,...
1, n , h,...

my question is:
Concatenate Column Values from Multiple Rows into a Single Column ?
e.g.
ID, Field2,
1 , x, z, n,
or
ID, Field1
1 , y,m,h

Thank in advance

 

[Jon K]

To concatenate the fields, you will need some VBA code.


I have created a function Conc() in a Module in the attached database.

The database also contains three queries:-
"qryOne" returns Field1 and Field2 for ID=1 from table tblData (to replicate your sample data).

"qryTwo" is a Totals query. It uses the function Conc() to concatenate the results of qryOne.

A third query shows how to use the function on the table.


You can open the database and run qryTwo and the third query.


Note The code assumes ID is a numeric field. If it is a text field, the line:-
" WHERE [" & Identity & "]=" & Value

must be changed to:-
" WHERE [" & Identity & "]='" & Value & "'"

 

[asg]

The solution worked like a charm. Thanks.

Now I have a similar problem, but more involved. I want to concatenate a field based on 2 criteria fields. Here's the table:

ID1; ID2; Text
1; X; aa
1; X; bb
1; Y; cc
1; Y; dd
2; X; ee
2; X; ff
2; Y; gg
2; Y; hh
2; Z; ii
2; Z; jj

I want to concatenate the Text field, separated by commas, and get a 2-dimensional table as output:

ID; X; Y; Z
1; aa, bb; cc, dd;
2; ee, ff; gg, hh; ii, jj

In words, I want to concatenate the Text field for every pair ID1, ID2. I hope that makes sense.

I don't know enough SQL and VBA to either invent my own code or extend the code already given. Any help is greatly appreciated.

In case it makes a difference, I am using Access 2002.

Thanks.

 

[the_rookie]

Hi, I have a similar problem, and the given solution seems to work. But I'm new to adding VBA stuff to access, so can someone teach m,e how to add a VBA script to a access database? thanks!

 

[PNGBill]

Concatenating fields can also be done easily with sql or query design.

This query produces the body of a letter and uses a combination of "text" and [fields] to make up a line on the letter.

The query creates some 15 odd new fields of text.

To simply join two fields use [fieldname1]&[fieldname2] as NewfieldName

To add words you would use "Dear "&[firstname]&" "&[lastname]&"," as NewFieldName, to get Dear Bob Smith,

SELECT TBLLOAN.LDPK, [ADFirstName] & " " & [ADSurname] AS FullName, TBLEMPDET.EDName, TBLEMPDET.EDPost1, TBLEMPDET.EDPost2, TBLEMPDET.EDPost3, "data###### has granted a personal loan, LID " & [LDPK] & ", to myself and I authorise and" AS Line01, "request that commencing " & [LDST] & " the amount of K" & [LDPayK] & " be deducted from my pay" AS Line02, "every " & (Left([LDPayFre],Len([LDPayFre])-2)) & " for the next " & [LDPayNo] & " pay periods." AS Line03, "The deductions are to be paid to######## by company cheque / direct credit." AS Line03a, "In the event of my resignation or termination, you are authorised and instructed to pay from " AS Line04, "my entitlements the balance owed to ######### after any debts owing to " AS Line05, [EDName] & " are settled in full and before paying myself." AS Line05a, "This Authority is not to be canceled without the written approval of Club Group Limited." AS Line06, "Please confirm to ####### that the above request will be carried out" AS Line07, "by signing as indicated below." AS Line07a, "Payments to be made to #######," AS Line08, "ANZ Port Moresby Account ####### or BSP Boroko Account ########" AS Line09, "I also advise that should I not make the first repayment on or before " & ([LDSt]+14) & " I will have to" AS Line10, "pay a Late Fee of K" & ([LoanLateFee]*2) & " and should any further repayments be missed and or the loan" AS Line10a, "is not completed on or before " & [LDDTE] & " then I will be charged K" & [LoanLateFee] & " for every fortnight" AS Line11, "missed and every fortnight there after until the loan is completed." AS Line12, "I authorise these late fees to be deducted from my pay and paid to ########." AS Line13, TBLEMPDET.EDDedLetRef, TBLACCDET.EmployeeNumber
FROM TBLLOAN INNER JOIN (TBLACCDET INNER JOIN TBLEMPDET ON TBLACCDET.EDPK = TBLEMPDET.EDPK) ON TBLLOAN.ADPK = TBLACCDET.ADPK;

 

[the_rookie]

actually i want to concatnate multiple records, not fields...
Ex:ID, Field1, Field2,...
1 , x , y,...
1, z , m,...
1, n , h,...
converted to --
ID, Field2,
1 , x, z, n,
Can someone tell me how i can do this? thanks

 

[PNGBill]

This is what is happening. When we say fields we mean the records in the fields.

Say you want to select only the people living in New York then you use the same query but Where at the end to restrict records to just new York residents.

 

[the_rookie]

true, but i want all these records to be concatnated into one

Fileld1 Field2 Feild 3
1 a x
1 b y
1 c z

should become
Fileld1 Field2 Feild 3
1 a,b,c x,y,z

 

[PNGBill]

"my question is:
Concatenate Column Values from Multiple Rows into a Single Column ?"

Could be written

"*** Concatenate multiply fields into one field ?"

Your Rows are Records. Each record is made up of one or more fields.

Should, for some reason, you want the data from one record's field to be joined to data on another record's field (same field or different field) you may have to use a make table query and Update Queries to collect the data prior to a select query assembling and concatenating to get your result.
This would be like joining John Smith to Fred Jone's residential address.

 

[PNGBill]

true, but i want all these records to be concatnated into one

Fileld1 Field2 Feild 3
1 a x
1 b y
1 c z

should become
Fileld1 Field2 Feild 3
1 a,b,c x,y,z

What is your Primary Key ?? They can't all have 1 as the unique record field?

How many records and fields are you talking about ?

 

[the_rookie]

Neither of these feilds is the PK. (its something else)there will be a maximum of 7-8 records that will be concatnated into one.

 

[PNGBill]

Apart from really making sure there is not some underlying fault with your database structure then You either have to use some VBA code to collect the data or as I mentioned, create a temporary table and assemble the data one record at a time.

You will need to supply the field names and data type plus include the primary key field.

The make table query will make a new table with 21 fields and the first three will have the data from your first record.
Then an Update query will put the data from the 2nd record into the next three fields in your temporary table and so on.

When finished a select query will concatenate the fields data into one new field.

Very messy.

Have you tried a Cross tab query ?

 

[mihalisp]

Hi everybody,

Jon K this is an excellent piece of code that has saved me a lot of times.

But i want to ask for something more complicated if anyone can help me in this forum.

I need a small adjustment of that piece of code but i am confused.

I want the concatenated field to include only distinct values

for example,when i concatenate multiple rows and the result is something like this: 1,1,2,1,3,3,4,1,3,2

i would prefer the rusult be 1,2,3,4

I tried to change the piece of code that refers to:

Do While Not rs.EOF
If Not IsNull(rs!Fld) Then

vFld = vFld & ", " & rs!Fld

End If
rs.MoveNext
Loop

but i got confused and couldnt make it work!

Can anyone help me??

Thank you all in advance!!

 

[LJEsposito]

I posted a reply but I'm not sure why I don't see it or why this forum times me out so quickly!

To Jon K - Your code was perfect once I found out how to update VBA references in 2007 to accomodate the ADO.

To mihalisp - I had suggested you try to use the Query > Totals > Group By functionality to eliminate your duplicate values before leveraging Jon K's Conc Function.

Thanks again,
Larry

 

[neophyte]

I downloaded Jon K's database and it is very close to doing what I need. However, I need to create another access table from the results so I need to limit the number of characters in each output field to 255. The problem is that with my data the output field is too long. In my case, Field1 can be up to 23 characters (including comma separator) and I have up to 278 records for an ID. Field2 can be up to 7 characters (including comma separator). So I need the query to start a new line/record after 11 Field1s (255/23) or after 36 Field2s (255/7). Is it possible to modify Jon K's solution to accomplish this? If I simply run the current query as a make table query it truncates the Field1 and Field2 output at 255 characters.

 

[daliaj]

This is a great code. I have been looking for this for the last 4 months. My primary field is 'Date'.

What is the change in code if ID is a 'Date' field?

Thank you. I appreciate your response.

 

[biobee]

Hi Jon K,

Thanks a lot for sharing this VBA code. I was looking for exactly something like this for a similar problem of collapsing multiple rows to a single row. I need a little help though. After running the VBA code on my query I get the collapsed rows, however for many rows the values are truncated. Is there a way that I can get all the values. Maybe increase the maximum length allowed per row.
As an example A1 has 12 rows :
NameIdentityChromosomeStrandCount_of_Probe_NamePosition
A1100.00%X+12(119911603-119911701)A1100.00%X+12(119916463-119916561)A1100.00%X+12(119921346-119921444)A1100.00%X+12(119926207-119926305)A1100.00%X+12(119931067-119931165)A1100.00%X+12(119935927-119936025)A1100.00%X+12(119940787-119940885)A1100.00%X+12(119945647-119945745)A1100.00%X+12(119906742-119906840)A1100.00%X+12(119901882-119901980)A1100.00%X+12(119897021-119897119)A199.00%X+12(119892114-119892212)

When the rows are collapsed into one row after running the code, I get the following result. However under the Position column, the values is truncated.
NameIdentityChromosomePositionStrandcountCT47dot1100.00%, 100.00%, 100.00%, 100.00%, 100.00%, 100.00%, 100.00%, 100.00%, 100.00%, 100.00%, 100.00%, 99.00%X, X, X, X, X, X, X, X, X, X, X, X(119911603-119911701), (119916463-119916561), (119921346-119921444), (119926207-119926305), (119931067-119931165), (119935927-119936025), (119940787-119940885), (119945647-119945745), (119906742-119906840), (119901882-119901980), (119897021-119897119), (1+, +, +, +, +, +, +, +, +, +, +, +12

How do I get all the values?

Thanks

To concatenate the fields, you will need some VBA code.


I have created a function Conc() in a Module in the attached database.

The database also contains three queries:-
"qryOne" returns Field1 and Field2 for ID=1 from table tblData (to replicate your sample data).

"qryTwo" is a Totals query. It uses the function Conc() to concatenate the results of qryOne.

A third query shows how to use the function on the table.


You can open the database and run qryTwo and the third query.


Note The code assumes ID is a numeric field. If it is a text field, the line:-
" WHERE [" & Identity & "]=" & Value

must be changed to:-
" WHERE [" & Identity & "]='" & Value & "'"

 

[Zadotchi]

HI All
I like the code, and I tried it on my database, 'I get a compile error with this line of code saying "User-defined type not defined"
and the Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection is highlighted

I already created the Module1 which include to Conc() FUnction

Any Idea, I will try to search it online for solution, but if someone has quick fix, please help

Regards

To concatenate the fields, you will need some VBA code.


I have created a function Conc() in a Module in the attached database.

The database also contains three queries:-
"qryOne" returns Field1 and Field2 for ID=1 from table tblData (to replicate your sample data).

"qryTwo" is a Totals query. It uses the function Conc() to concatenate the results of qryOne.

A third query shows how to use the function on the table.


You can open the database and run qryTwo and the third query.


Note The code assumes ID is a numeric field. If it is a text field, the line:-
" WHERE [" & Identity & "]=" & Value

must be changed to:-
" WHERE [" & Identity & "]='" & Value & "'"

 

[biobee]

HI All
I like the code, and I tried it on my database, 'I get a compile error with this line of code saying "User-defined type not defined"
and the Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection is highlighted

I already created the Module1 which include to Conc() FUnction

Any Idea, I will try to search it online for solution, but if someone has quick fix, please help

Regards

Hi,

You see the error message because one of the following reasons:
1. One or more of your columns doesnot have the right format property ie. the column has a mix of text and numeric or something else
2. The SQL statement that you have created (go to SQL view in query mode) is not complete and has one of the handles wrong.
If you share your data table and the SQL command you have used, then I can probably give you a more specific answer.

 

[Zadotchi]

In Fact I imported the original table tblData and all three quesries and Module1 to my database, and it gave the same error when I execute either imported quesr

when I run it from your database on my desktop it works fine

Yesterday I had copy of my work database, it gave the same error, @ work after it the error and when I click OK and try to close the code widows it warn me that that will stop the debuger and reloop into the same error again, where i have to use task manager to close the DB

Any Clue

Thanks

Hi,

You see the error message because one of the following reasons:
1. One or more of your columns doesnot have the right format property ie. the column has a mix of text and numeric or something else
2. The SQL statement that you have created (go to SQL view in query mode) is not complete and has one of the handles wrong.
If you share your data table and the SQL command you have used, then I can probably give you a more specific answer.