How to concatenate two columns using vba code

[LakshSudha]

The output sholud display the abbreviated name with comma separator. If the project does not have abbreviated name means it should take its secondary_Appli cation name. I need the VB macro for the output table.


I have a source table like below

PROJECT_ID   SECONDARY_APPLICATION   ABBREVIATED_NAME
1000         3D Static                 SDSSPP
1000         ACA	
1000         Multi Rater               MR
1001         .Net framework
1001         ACCT4
1002         WAM                       WAM
1002         HOD                       HOD
1002         HOD/WAM                   HOD/WAM

I need the output like the below one

PROJECT_ID	   ADDITIONAL_APPLICATION
1000                  SDSSPP,ACA,MR
1001                  .Net framework, ACCT4
1002                  WAM,HOD,HOD/WAM

Anyone can help me in this????

[zmbd]

Terminology first:

Macro - In Access one of the two totally separate programing languages. This language is mostly avoided unless one is posting to a SharePoint site.

VBA - In Access one of the two totally separate programming languages. Most Applications development is undertaken using this programing language.

In Access, VBA is NOT the same as Macro. One can call the other using various methods. Of the two, VBA is widely considered to be more robust and useful than the Access-Macro

Second:
I think you can do this with just a cross-tab query and then maybe a select query afterwards to combine the results; however, give me a few moments to think this thru as the logic isn't clear in my head yet... I'm a little slower on the SQL than the others. (^_^) Rabbit might be along and I think he dreams in SQL (^_^)
In the meantime take a look at: •Crosstab query techniques - row totals, zeros, parameters, column headings - Allen Browne


as for the code... please be aware that we don't normally give out code to start with - think of it as teaching to fish.

The basic outline for would be:
Create a table with the field names you need, is very important to keep in mind when naming fields it is preferable to use alphanumeric and preferably no spaces just underscores and avoid:Access 2007 reserved words and symbols and Problem names and reserved words in Access (AllenBrowne)

Next open your VBA editor (alt-f11) and make sure that that the Options are properly set ([*]> Before Posting (VBA or SQL) Code):

Then what I would do here is
Open a DAO record set on your table
- one record set grouped by PROJECT_ID
- second record set that would be feed by outside loop
- third record set that would hold a filter of second.
Move to the first record in both record sets
Outside loop - pull PROJECT_ID
save to new table
inside loop - set the second recordset using PROJECT_ID from the outside loop to pull just those records with that PROJECT_ID. setup the filter on this recordset and (third recordset) and return count for records ABBREVIATED_NAM E <> to null or empty-string.
Depending on this count, either step thru the filtered set or the unfiltered set and pull and concatenate the proper fields.
Once done, then store result to the table.
Release the inside loop record sets
Step the outer loop
repeat.

[LakshSudha]

Hi.. Actually the concept is to concatenate the abbreviated_ name with comma separator.If the project id does not have any abbreviated_nam e then it should take its secondary_appli cation name.
Example : 1. project_id 1000 has only two abbreviated_nam e and it does not have the abbreviated name for ACA. So in the output it has taken the secondary_appli cation name which does not have the abbreviated name. finally the output for project_id 1000 is [SDSSPP(abbrevia ted_name),ACA(S econdary_Applic ation),MR)abbre viated_name ].

2. project_id 1001 does not have any abbreviated_nam e. It has taken its secondary_appli cation name with comma separator in the output. So the output for this id is [ .Net framework(secon dary_applicatio n), ACCT4(secondary _application)].


3. Project_id 1002 has all the abbreviated names. so no need to consider secondary_appli cation name here. We can directly concatenate those abbreviated_nam es with comma separator. the output for this id is [ WAM, HOD, HOD/WAM ]


I think now you can easily understand the concept. I need a vb code for this concept. I have to execute that code in MS ACCESS 2007. As i'm new to vb i dont know how to develop the code for this concept. The below coding is the one which i have wrote. But it is not working..

VB CODE :

Option Compare Database
Option Explicit

Public Sub CreateAddAppsTableCSVList()
On Error Resume Next

Dim db As DAO.Database
Dim tableRecord As DAO.Recordset
Dim queryTableData As String
Dim strPROJECT_ID As String
Dim strSECONDARY_APPLICATION As String
Dim strABBREVIATED_NAME As String


Set db = CurrentDb()

queryTableData = "SELECT * FROM DV_SECONDARY_APPLICATION"
Set tableRecord = db.OpenRecordset(queryTableData, dbOpenSnapshot)

If Not tableRecord.BOF And Not tableRecord.EOF Then

  tableRecord.MoveFirst



  strPROJECT_ID = tableRecord!PROJECT_ID
  strSECONDARY_APPLICATION = tableRecord!SECONDARY_APPLICATION
  strABBREVIATED_NAME = tableRecord!ABBREVIATED_NAME
  
  tableRecord.MoveNext

  Do Until tableRecord.EOF
    
    If strPROJECT_ID = tableRecord!PROJECT_ID Then

        If strABBREVIATED_NAME <> Null Then
            strABBREVIATED_NAME = strABBREVIATED_NAME & "," & tableRecord!ABBREVIATED_NAME
        Else
            strABBREVIATED_NAME = tableRecord!SECONDARY_APPLICATION
        End If

        Else
        
        queryTableData = "INSERT INTO DV_ ABBR_SECONDARY_APPLICATIONS (PROJECT_ID, ADDITIONAL_APPLICATIONS" & "VALUES(&strPROJECT_ID,       &strABBREVIATED_NAME )"

            db.Execute queryTableData
         tableRecord.MoveNext
    End If
  Loop

End If

Set tableRecord = Nothing
Set db = Nothing

End Sub

Any help is greatly appreciated..

[zmbd]

Yes, I understood what you wanted.
You want to conditionally flatten the records, not an issue using VBA

Here's a partial code
>This will not run as is, you will need to finish coding

Option Compare Database
Option Explicit
 
Public Sub CreateAddAppsTableCSVList()
On Error Resume Next
'
Dim zdb As DAO.Database
Dim zrsoutsideloop As DAO.Recordset
Dim zrsinsideloop As DAO.Recordset
Dim zrsinsideloopfiltered As DAO.Recordset
Dim zrsholdingtable As DAO.Recordset
'
Dim zSQL As String
'
Dim zX As Integer
'
Set zdb = CurrentDb()
'
'
On Error GoTo zonerror
'Open recordset to the holding table
zSQL = "SELECT tbl_hold.project_id" & _
        ", tbl_hold.concatenated_Name" & _
        "FROM tbl_hold;"
'We'll need to add records to this so dynaset
Set zrsholdingtable = zdb.OpenRecordset(zSQL, dbOpenDynaset)
'
'Open recordset for the outside loop
zSQL = "SELECT DV_Secondary_Application.project_id" & _
        "FROM DV_Secondary_Application" & _
        "GROUP BY DV_Secondary_Application.project_id;"
'
'if there are any records, we're only go go forward so forwardonly
Set zrsoutsideloop = zdb.OpenRecordset(zSQL, dbOpenForwardOnly)
'
'if there is any record in the outside loop then feed to the inside loop
If zrsoutsideloop.RecordCount Then
    Do
        'open the recordset to the inside loop using the [zrsoutsideloop]![project_id] in the where clause
        'filter the inside recordset on the abbreviated_name field using the rs.filter method
        'if there are records in the filtered set then concatenate records to string
        'else concatenate the secondary_application field record entries
        '... hint there's a second do until loop in here.
        'add a new record to zrsholdingtable pulling the [zrsoutsideloop]![project_id] and the concatenate to string
       'hint: zrshold.add method
'
    Loop Until zrsoutsideloop.EOF
End If
'
'
zcloseandclean:
If Not zrsoutsideloop Is Nothing Then
    zrsoutsideloop.Close
    Set zrsoutsideloop = Nothing
End If
'
If Not zrsinsideloop Is Nothing Then
    zrsinsideloop.Close
    Set zrsinsideloop = Nothing
End If
'
If Not zrsinsideloopfiltered Is Nothing Then
    zrsinsideloopfiltered.Close
    Set zrsinsideloopfiltered = Nothing
End If
'
If Not zrsholdingtable Is Nothing Then
    zrsholdingtable.Close
    Set zrsholdingtable = Nothing
End If
'
'exit sub
zonerror:
MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & Err.Source
Resume zcloseandclean
End Sub

If I get a chance later I might be able to finish the inside loops; however, you should be able to figure it out from here

[LakshSudha]

I have worked on this one and got the output now.. Thanks a lot for your help :)

[zmbd]

That's Great!

I've finally had a chance to sit down and finish the code posted earlier.

This does require that a "holding table" be created by hand as follows:
tbl_Hold
[hold_pk] autonumber primary key
[project_id] Numeric(long)
[concatenated_Na me] text(255)

Option Compare Database
Option Explicit
 
Public Sub CreateAddAppsTableCSVList()
On Error Resume Next
'
Dim zdb As DAO.Database
Dim zrsoutsideloop As DAO.Recordset
Dim zrsinsideloop As DAO.Recordset
Dim zrsinsideloopfiltered As DAO.Recordset Dim zrsholdingtable As DAO.Recordset '
Dim zSQL As String
'
Dim zX As Integer
'
Set zdb = CurrentDb()
'
'
On Error GoTo zonerror
'Open recordset to the holding table
zSQL = "SELECT tbl_hold.project_id" & _
        ", tbl_hold.concatenated_Name" & _
        " FROM tbl_hold;"
'We'll need to add records to this so dynaset Set zrsholdingtable = zdb.OpenRecordset(zSQL, dbOpenDynaset) With zrsholdingtable
    If .RecordCount Then
        .MoveLast
        .MoveFirst
        MsgBox "Current Holding Table Count: " & zrsholdingtable.RecordCount
        .MoveLast
    End If
End With
'
'
'Open recordset for the outside loop
zSQL = "SELECT DV_Secondary_Application.project_id" & _
        " FROM DV_Secondary_Application" & _
        " GROUP BY DV_Secondary_Application.project_id;"
'
'if there are any records, we're only go go forward so forwardonly Set zrsoutsideloop = zdb.OpenRecordset(zSQL, dbOpenForwardOnly) '
'if there is any record in the outside loop then feed to the inside loop If zrsoutsideloop.RecordCount Then
    Do
        'open the recordset to the inside loop using the [zrsoutsideloop]![project_id] in the where clause
        zSQL = "SELECT DV_Secondary_Application.Secondary_Application" & _
            ", DV_Secondary_Application.Abbreviated_Name" & _
            " FROM DV_Secondary_Application" & _
            " WHERE (((DV_Secondary_Application.project_id)=" & zrsoutsideloop![project_id] & "));"
        Set zrsinsideloop = zdb.OpenRecordset(zSQL, dbOpenDynaset)
        'filter the inside recordset on the abbreviated_name field using the rs.filter method
        zSQL = "SELECT DV_Secondary_Application.Secondary_Application" & _
            ", DV_Secondary_Application.Abbreviated_Name FROM DV_Secondary_Application" & _
            " WHERE(" & _
                " (DV_Secondary_Application.project_id=" & zrsoutsideloop![project_id] & ")" & _
                " AND" & _
                    "((DV_Secondary_Application.Abbreviated_Name) Is Not Null)" & _
                " OR" & _
                    " (DV_Secondary_Application.Abbreviated_Name)='');"
        Debug.Print zSQL
        Set zrsinsideloopfiltered = zdb.OpenRecordset(zSQL, dbOpenDynaset)
        'if there are records in the filtered set then concatenate records to string
        zSQL = ""
        If zrsinsideloopfiltered.RecordCount Then
            With zrsinsideloopfiltered
                .MoveLast
                .MoveFirst
                Do
                    zSQL = zSQL & ![Abbreviated_Name] & ","
                    .MoveNext
                Loop Until .EOF
            End With
        Else
            With zrsinsideloop
                .MoveLast
                .MoveFirst
                Do
                    zSQL = zSQL & ![Secondary_Application] & ","
                    .MoveNext
                Loop Until .EOF
            End With
        End If
        'remove trailing comma
        zSQL = Left(zSQL, (Len(zSQL) - 1))
        'add a new record to zrsholdingtable pulling the [zrsoutsideloop]![project_id] and the concatenate to string
        With zrsholdingtable
            .AddNew
            !project_id = zrsoutsideloop![project_id]
            !concatenated_Name = zSQL
            .Update
        End With
        zrsoutsideloop.MoveNext
    Loop Until zrsoutsideloop.EOF
End If
'
MsgBox "Current Holding Table Count: " & zrsholdingtable.RecordCount '
'
zcloseandclean:
If Not zrsoutsideloop Is Nothing Then
    zrsoutsideloop.Close
    Set zrsoutsideloop = Nothing
End If
'
If Not zrsinsideloop Is Nothing Then
    zrsinsideloop.Close
    Set zrsinsideloop = Nothing
End If
'
If Not zrsinsideloopfiltered Is Nothing Then
    zrsinsideloopfiltered.Close
    Set zrsinsideloopfiltered = Nothing
End If
'
If Not zrsholdingtable Is Nothing Then
    zrsholdingtable.Close
    Set zrsholdingtable = Nothing
End If
'
Exit Sub
zonerror:
MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & Err.Source Resume zcloseandclean
End Sub

[jaklodhi]

Thanks for the code

Thanks for the code guys it worked like wonder.

That's Great!

I've finally had a chance to sit down and finish the code posted earlier.

This does require that a "holding table" be created by hand as follows:
tbl_Hold
[hold_pk] autonumber primary key
[project_id] Numeric(long)
[concatenated_Na me] text(255)

Option Compare Database
Option Explicit
 
Public Sub CreateAddAppsTableCSVList()
On Error Resume Next
'
Dim zdb As DAO.Database
Dim zrsoutsideloop As DAO.Recordset
Dim zrsinsideloop As DAO.Recordset
Dim zrsinsideloopfiltered As DAO.Recordset Dim zrsholdingtable As DAO.Recordset '
Dim zSQL As String
'
Dim zX As Integer
'
Set zdb = CurrentDb()
'
'
On Error GoTo zonerror
'Open recordset to the holding table
zSQL = "SELECT tbl_hold.project_id" & _
        ", tbl_hold.concatenated_Name" & _
        " FROM tbl_hold;"
'We'll need to add records to this so dynaset Set zrsholdingtable = zdb.OpenRecordset(zSQL, dbOpenDynaset) With zrsholdingtable
    If .RecordCount Then
        .MoveLast
        .MoveFirst
        MsgBox "Current Holding Table Count: " & zrsholdingtable.RecordCount
        .MoveLast
    End If
End With
'
'
'Open recordset for the outside loop
zSQL = "SELECT DV_Secondary_Application.project_id" & _
        " FROM DV_Secondary_Application" & _
        " GROUP BY DV_Secondary_Application.project_id;"
'
'if there are any records, we're only go go forward so forwardonly Set zrsoutsideloop = zdb.OpenRecordset(zSQL, dbOpenForwardOnly) '
'if there is any record in the outside loop then feed to the inside loop If zrsoutsideloop.RecordCount Then
    Do
        'open the recordset to the inside loop using the [zrsoutsideloop]![project_id] in the where clause
        zSQL = "SELECT DV_Secondary_Application.Secondary_Application" & _
            ", DV_Secondary_Application.Abbreviated_Name" & _
            " FROM DV_Secondary_Application" & _
            " WHERE (((DV_Secondary_Application.project_id)=" & zrsoutsideloop![project_id] & "));"
        Set zrsinsideloop = zdb.OpenRecordset(zSQL, dbOpenDynaset)
        'filter the inside recordset on the abbreviated_name field using the rs.filter method
        zSQL = "SELECT DV_Secondary_Application.Secondary_Application" & _
            ", DV_Secondary_Application.Abbreviated_Name FROM DV_Secondary_Application" & _
            " WHERE(" & _
                " (DV_Secondary_Application.project_id=" & zrsoutsideloop![project_id] & ")" & _
                " AND" & _
                    "((DV_Secondary_Application.Abbreviated_Name) Is Not Null)" & _
                " OR" & _
                    " (DV_Secondary_Application.Abbreviated_Name)='');"
        Debug.Print zSQL
        Set zrsinsideloopfiltered = zdb.OpenRecordset(zSQL, dbOpenDynaset)
        'if there are records in the filtered set then concatenate records to string
        zSQL = ""
        If zrsinsideloopfiltered.RecordCount Then
            With zrsinsideloopfiltered
                .MoveLast
                .MoveFirst
                Do
                    zSQL = zSQL & ![Abbreviated_Name] & ","
                    .MoveNext
                Loop Until .EOF
            End With
        Else
            With zrsinsideloop
                .MoveLast
                .MoveFirst
                Do
                    zSQL = zSQL & ![Secondary_Application] & ","
                    .MoveNext
                Loop Until .EOF
            End With
        End If
        'remove trailing comma
        zSQL = Left(zSQL, (Len(zSQL) - 1))
        'add a new record to zrsholdingtable pulling the [zrsoutsideloop]![project_id] and the concatenate to string
        With zrsholdingtable
            .AddNew
            !project_id = zrsoutsideloop![project_id]
            !concatenated_Name = zSQL
            .Update
        End With
        zrsoutsideloop.MoveNext
    Loop Until zrsoutsideloop.EOF
End If
'
MsgBox "Current Holding Table Count: " & zrsholdingtable.RecordCount '
'
zcloseandclean:
If Not zrsoutsideloop Is Nothing Then
    zrsoutsideloop.Close
    Set zrsoutsideloop = Nothing
End If
'
If Not zrsinsideloop Is Nothing Then
    zrsinsideloop.Close
    Set zrsinsideloop = Nothing
End If
'
If Not zrsinsideloopfiltered Is Nothing Then
    zrsinsideloopfiltered.Close
    Set zrsinsideloopfiltered = Nothing
End If
'
If Not zrsholdingtable Is Nothing Then
    zrsholdingtable.Close
    Set zrsholdingtable = Nothing
End If
'
Exit Sub
zonerror:
MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & Err.Source Resume zcloseandclean
End Sub