Concatenate Column Values from Multiple Rows into a Single Column

[Zadotchi]

HI BioBee
finaly I got the code to work after I reference to Microsoft Active Library 2.1

but the result is not right
here is what my data looks like
tblCourses
SeqNo Stu_Id Year Semester Course_No Curse_Name Status Grade
1 1 2011 Spring S_11_01 IT_S_11_1 Pass 3.2
2 1 2011 Spring S_11_02 IT_S_11_2 Pass 3.4
3 1 2011 Fall F_11_01 IT_F_11_1 in progress
4 1 2011 Fall F_11_02 IT_F_11_2 in progress
5 2 2010 Fall F_10_01 IT_F_10_1 Pass 2.6
6 2 2010 Fall F_10_02 IT_F_10_2 Pass 2.8
7 2 2011 Spring S_11_05 IT_S_11_5 Pass 3.1

QryTerm (from tblCourses) (3 fields)
1. Student_ID
2. TermQ: [Semester] & [Year]
3. CourseDetail: [Course_No] & "-" & [Course_Name] & ", Status " & [Status] & " - Grade:" & [Course_Grade]
field 3 will concatinate the info of 1 single course
output like
1 Spring2011 S_11_01, IT_S_11_1, Pass, 3.2
1 Spring2011 S_11_02 IT_S_11_2, Pass, 3.4
1 Fall2011 F_11_01 IT_F_11_1, in progress,
1 Fall2011 F_11_02 IT_F_11_2, in progress,
2 Fall2010 F_10_01 IT_F_10_1, Pass, 2.6
2 Fall2011 F_10_02 IT_F_10_2, Pass, 2.8
2 Spring2011 S_11_05 IT_S_11_5, Pass, 3.1

FInal Quary to concatinate all courses in a semester for specific student
QryFinal from QryTerm (3 fields)
Student_ID
TermQ
CoursesAllInOne: Conc("CourseDetail","Student_Id",[Student_Id],"QryTerm")
Output like this
1 Spring2011 S_11_01, IT_S_11_1, Pass, 3.2, S_11_02 IT_S_11_2, Pass, 3.4
1 Fall2011 S_11_01, IT_S_11_1, Pass, 3.2, S_11_02 IT_S_11_2, Pass, 3.4
2 Fall2011 F_10_01 IT_F_10_1, Pass, 2.6
2 Spring2011 F_10_01 IT_F_10_1, Pass, 2.6

it does the concatination for the first record correctly and output it for the second (same Student different Term)
then when it loop for the second student it does the same thing

any clue, what wrong I did
did I put the parameter for the conc function correctly
Please help
Thanks in Advance for your time and help

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

 

[sclark1234]

Just in case anyone runs into the same error I was having...It was giving me a compile error for the line below:

vFld = Mid(vFld, 3)

change it to:

vFld = VBA.Mid(vFld, 3)

and it works fine. Thanks so much for this post!!!!!!

 

[BrianB75]

Just wanted to say thanks for posting this information. This has worked great and save me quite a few headaches.

 

[hycho]

I've found Jon K's database/VBA codes to be very useful. However, since I switched to a new computer, I am no longer able to run his codes.

When I do run it, I would get this error message:

Microsoft Visual Basic
Run-Time error '-2147221164 (80040154)':
Class not registered

And when I hit the debug button, the blue underline section of the code below is highlighted in yellow.

Does anyone know how I can resolve this issue? Do I need to download/register something?


Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null

SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value

' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & ", " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 3)

Set cnn = Nothing
Set rs = Nothing

' return concatenated string.
Conc = vFld
End Function

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 & "'"

Thanks.

 

[boblarson]

Looks like you need to set a reference to ADO, but if it is already set, then it may have to do with something Microsoft caused by changing the registry entries for ADO.

See here for more about that:
http://social.msdn.microsoft.com/Fo...s/thread/3a4ce946-effa-4f77-98a6-34f11c6b5a13

 

[hycho]

Thanks, I tried downloading the update package from the website below (after scrolling to the bottom of http://social.msdn.microsoft.com/For...6-34f11c6b5a13), but I can't open the file because it has an .msu extension. I was then prompted into the website below, but I don't quite understand the instruction. Is there another way I can open the file or where can I find the window command prompt?

http://www.fileinfo.com/extension/msu

http://www.microsoft.com/en-us/download/confirmation.aspx?id=28453

Thanks.

 

[hycho]

If anyone can write a better concatenate code than above, I will be happy to use the new method as well, since the current method does not fit exactly what I need to do.

Here's the data I have:

ID Language
1 English
1 Spanish
2 English
2 French

Here's the output I want (notice I now have two language columns instead of one):

ID Language1 Language2
1 English Spanish
2 English French

Thanks for your help.

 

[boblarson]

Sorry, the actual easy fix for the reference problem is just to compile (run DEBUG > COMPILE) on a computer that uses Windows 7 SP1 if the database is going to be used there, or do it on Windows 7 no SP if that is where it is goinb to be used. The problem comes when mixing the two.

 

[hycho]

Sorry, the actual easy fix for the reference problem is just to compile (run DEBUG > COMPILE) on a computer that uses Windows 7 SP1 if the database is going to be used there, or do it on Windows 7 no SP if that is where it is goinb to be used. The problem comes when mixing the two.

Thanks for the help, but when I try compiling, I do not get any errors. But when I run the query, I will get an error, and I will hit debug. And the section below is highlighted.

rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

FYI, I am using Windows XP 2002 and I have MS Access 2003.

Thank again.

 

[boblarson]

Sounds to me that something may not be correct with the SQL statement. Before that line put:

Debug.Print SQL

to see what it outputs (and then paste that here so we can see).

 

[hycho]

I tried putting the line into the section below, and compiled the code, I got the error message "compile error: variable not defined." As a FYI, I am thinking my new computer is missing some softwares/packet/updates.

Thanks.

See below for where I placed Debug.Print SQL

Option Compare Database
Option Explicit

Public Function Conc(Fieldx, Identity, Value, Source) As Variant

Debug.Print SQL

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

 

[boblarson]

I tried putting the line into the section below, and compiled the code, I got the error message "compile error: variable not defined." As a FYI, I am thinking my new computer is missing some softwares/packet/updates.

Thanks.

See below for where I placed Debug.Print SQL

Option Compare Database
Option Explicit

Public Function Conc(Fieldx, Identity, Value, Source) As Variant

Debug.Print SQL

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

Well, that isn't going to help at all. I told you to put it just before THIS LINE:
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

like this

[B]Debug.Print SQL[/B]
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

it has to be right before that line so we know what you are trying to pass to it. Putting it at the top is going to yield NOTHING. It will be BLANK at that point.

 

[hycho]

I tried your suggestions (see below), and when I compiled the code, nothing happened, as in I am guessing there is no error. But when I ran the query, I will get an error message, and I will hit the debug button. This takes me to the code below, with the "rs.open ..." line being highlighted in yellow.


Debug.Print SQL
' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly


Any other suggestions?

Thanks.

 

[boblarson]

Any other suggestions?

Yeah, copy and paste from the IMMEDIATE WINDOW (if you're not sure what that is, here is a pic to show you )
the SQL string that the Debug.Print SQL code should be outputting in the Immediate Window.

 

[hycho]

I open the "immediate window" screen, and compiled the code again with the debug line in the right place (see below). When I compiled, I do not see any output on the "immediate window."


Debug.Print SQL
' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

Thanks again.

 

[boblarson]

When I compiled, I do not see any output on the "immediate window."

Well that would indicate that SQL doesn't have anything in it, which would cause an error.

So we need to work at diagnosing why that is.

So, I guess we need to see how you are passing the parameters to the Conc function. How are you calling it and how are you passing the parameters?

 

[hycho]

I am running a query in the database called "qryTwo." The SQL code is below. I haven't modified the query nor the code. I only noticed the problem after I got a new computer, and tried running the same code and query. FYI, I still have access to my old computer, and it works fine.

SELECT [qryOne].[ID], Conc("Field1","ID",[ID],"qryOne") AS Field1, Conc("Field2","ID",[ID],"qryOne") AS Field2
FROM qryOne
GROUP BY [qryOne].[ID];

 

[boblarson]

I am running a query in the database called "qryTwo." The SQL code is below. I haven't modified the query nor the code. I only noticed the problem after I got a new computer, and tried running the same code and query. FYI, I still have access to my old computer, and it works fine.

SELECT [qryOne].[ID], Conc("Field1","ID",[ID],"qryOne") AS Field1, Conc("Field2","ID",[ID],"qryOne") AS Field2
FROM qryOne
GROUP BY [qryOne].[ID];

Is that the EXACT SQL you are using? Are your fields really named Field1 and Field2? (If so you need to rename them to something that more identifies what they are, and if not then you need to post exact what you have.)

And, after looking at the Conc code from Allen Browne's site (which I'm guessing you are using), you are not calling it correctly. If the names are the same as you have posted, it should be

Conc("Field1", "qryOne", "[ID]=" & [ID])

But if this works on another computer and not on the new one, have you tried going into the VBA Window and going to DEBUG > COMPILE <YourProjectNameHere>? It may return some errors which can be causing the problem.

 

[hycho]

Is that the EXACT SQL you are using? Are your fields really named Field1 and Field2? (If so you need to rename them to something that more identifies what they are, and if not then you need to post exact what you have.)

And, after looking at the Conc code from Allen Browne's site (which I'm guessing you are using), you are not calling it correctly. If the names are the same as you have posted, it should be

Conc("Field1", "qryOne", "[ID]=" & [ID])

But if this works on another computer and not on the new one, have you tried going into the VBA Window and going to DEBUG > COMPILE <YourProjectNameHere>? It may return some errors which can be causing the problem.

I downloaded the query and VBA code from John K's database on the first page of this thread, and I have modified the query since then for other uses.

Are you saying to compile the VBA code on my new PC? I have done that already, and no error messages. If you're talking about my old PC, then I will give that a try.

As a FYI, I looked into my old PC in the C:\WINDOWS\system32 and I have a lot more files in my old PC than my new PC, especially ones with .ocx and .dll extensions. Is it possible that once I move all those files from my old PC to my new PC, that would solve the problem? FYI, I am hoping that won't cause my new computer to be unstable and I have to get clearance from the IT department.

Thanks for your help.

 

[Galmala]

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 & "'"

Hello all,

I have been using Jon K's Conc function with great success utilizing a single NUMERIC field as the ID.

But I would like to Concantenate based on two fields (one numeric and one a date field) so instead of changing code significantly I thought of simply joining the date and numeric field into a new field (in a previous query) that would act as my ID field.

I notice in the thread above that I need to make a minor change to the code to work or a "text" ID field - unfortunately, the changes in the code dont seem to work.

Any suggestions would be greatly appreciated!

Cheers,

Chris