Concatenate Column Values from Multiple Rows into a Single Column (1 Viewer)

erncelen

New member
Joined
Mar 21, 2004
Messages
7
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

Registered User
Joined
May 22, 2002
Messages
2,209
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 & "'"
 

Attachments

asg

New member
Joined
Feb 12, 2010
Messages
1
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

New member
Joined
May 31, 2010
Messages
4
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

Win10 Office Pro 2016
Joined
Jul 15, 2008
Messages
2,269
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

New member
Joined
May 31, 2010
Messages
4
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
 
Last edited:

PNGBill

Win10 Office Pro 2016
Joined
Jul 15, 2008
Messages
2,269
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

New member
Joined
May 31, 2010
Messages
4
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

Win10 Office Pro 2016
Joined
Jul 15, 2008
Messages
2,269
"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

Win10 Office Pro 2016
Joined
Jul 15, 2008
Messages
2,269
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

New member
Joined
May 31, 2010
Messages
4
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

Win10 Office Pro 2016
Joined
Jul 15, 2008
Messages
2,269
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

Registered User
Joined
Oct 19, 2009
Messages
27
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

New member
Joined
Oct 25, 2010
Messages
4
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

New member
Joined
Feb 1, 2011
Messages
1
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

New member
Joined
May 16, 2011
Messages
1
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

New member
Joined
Jun 25, 2011
Messages
2
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

New member
Joined
Sep 28, 2011
Messages
3
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

New member
Joined
Jun 25, 2011
Messages
2
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

New member
Joined
Sep 28, 2011
Messages
3
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom