How do I add string values in the same column

[Guest]

I'm working in access and I have a table which has sequence numbers and text
associated with these sequence numbers. I would like to take the text for
these sequence no and combine them into one row. Example: four fields

MAT 101 1 This is
MAT 101 2 a basic
MAT 101 3 Mathmatics course.

I would like ....

MAT 101 This is a basic Mathmatics sourse.

R/S

DEL

 

[Guest]

[Field1] & " " & [Field2] & " " & [Field3] & " " & [Field4]

 

[Duane Hookom]

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

 

[Guest]

For some reason i've tried your module and it's not working for me ... I'm in
Access 2003. It gives me an error message each & every time. I thought it
might be the naming of the fields or even the naming of the tables ... but
nothing seemed to work. I can download your file and it works fine! Mine,
gives me the Subject, Course #, and Course Name ... but each time it errors
out on giving me a complete course description. Now, something I haven't
looked at is leading spaces but, i would think that it wouldn't matter. Now
the description could exceed 2000 characters.

 

[Guest]

The exact error message is: Microsoft Visual Basic - Run-time error
'-2147467259 (8004005); Method 'Open' of object '_Recordset' failed

Thanks!

 

[Duane Hookom]

Did you use the ADO version or DAO? There were lines of code to comment out
or un-comment based on your references to either ADO or DAO.

You might also want to reply back with your exact SQL syntax with the call
to the Concatenate function.

 

[Guest]

I believe I need to utilize the ADO version: (I'm strictly working within
access) The script is below.

Here is the statement I utilized:


Description: Concatenate("SELECT SCRTEXT_TEXT_CODE FROM 1a1 - Matched on
MaxofSCRTEXT_EFF_TERM WHERE COURSE_NAME =" & [COURSE_NAME] & " ORDER BY
SCRTEXT_SEQNO")

SCRTEXT_TEXT_CODE = The field I want to Concatenate
1a1 - Matched on MaxofSCRTEXT_EFF_TERM = The Table where the field resides
COURSE_NAME = Is the field which needs to remain unique.

Option Compare Database

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If

 

[Duane Hookom]

Don't ever put spaces in object names unless you always use []s around the
names. Try:

Description: Concatenate("SELECT SCRTEXT_TEXT_CODE FROM [1a1 - Matched on
MaxofSCRTEXT_EFF_TERM] WHERE COURSE_NAME =" & [COURSE_NAME] & " ORDER BY
SCRTEXT_SEQNO")

If COURSE_NAME is text, you must use delimiters:
Description: Concatenate("SELECT SCRTEXT_TEXT_CODE FROM [1a1 - Matched on
MaxofSCRTEXT_EFF_TERM] WHERE COURSE_NAME =""" & [COURSE_NAME] & """ ORDER BY
SCRTEXT_SEQNO")


--
Duane Hookom
MS Access MVP

I believe I need to utilize the ADO version: (I'm strictly working within
access) The script is below.

Here is the statement I utilized:


Description: Concatenate("SELECT SCRTEXT_TEXT_CODE FROM 1a1 - Matched on
MaxofSCRTEXT_EFF_TERM WHERE COURSE_NAME =" & [COURSE_NAME] & " ORDER BY
SCRTEXT_SEQNO")

SCRTEXT_TEXT_CODE = The field I want to Concatenate
1a1 - Matched on MaxofSCRTEXT_EFF_TERM = The Table where the field resides
COURSE_NAME = Is the field which needs to remain unique.

Option Compare Database

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If

Did you use the ADO version or DAO? There were lines of code to comment
out
or un-comment based on your references to either ADO or DAO.

You might also want to reply back with your exact SQL syntax with the
call
to the Concatenate function.