473,325 Members | 2,342 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

How to concatenate two columns using vba code

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


I have a source table like below

Expand|Select|Wrap|Line Numbers
  1. PROJECT_ID   SECONDARY_APPLICATION   ABBREVIATED_NAME
  2. 1000         3D Static                 SDSSPP
  3. 1000         ACA    
  4. 1000         Multi Rater               MR
  5. 1001         .Net framework
  6. 1001         ACCT4
  7. 1002         WAM                       WAM
  8. 1002         HOD                       HOD
  9. 1002         HOD/WAM                   HOD/WAM
I need the output like the below one

Expand|Select|Wrap|Line Numbers
  1. PROJECT_ID       ADDITIONAL_APPLICATION
  2. 1000                  SDSSPP,ACA,MR
  3. 1001                  .Net framework, ACCT4
  4. 1002                  WAM,HOD,HOD/WAM
Anyone can help me in this????
Apr 9 '14 #1
6 5167
zmbd
5,501 Expert Mod 4TB
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_NAME <> 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.
Apr 9 '14 #2
Hi.. Actually the concept is to concatenate the abbreviated_ name with comma separator.If the project id does not have any abbreviated_name then it should take its secondary_application name.
Example : 1. project_id 1000 has only two abbreviated_name and it does not have the abbreviated name for ACA. So in the output it has taken the secondary_application name which does not have the abbreviated name. finally the output for project_id 1000 is [SDSSPP(abbreviated_name),ACA(Secondary_Application ),MR)abbreviated_name ].

2. project_id 1001 does not have any abbreviated_name. It has taken its secondary_application name with comma separator in the output. So the output for this id is [ .Net framework(secondary_application), ACCT4(secondary_application)].


3. Project_id 1002 has all the abbreviated names. so no need to consider secondary_application name here. We can directly concatenate those abbreviated_names 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 :

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub CreateAddAppsTableCSVList()
  5. On Error Resume Next
  6.  
  7. Dim db As DAO.Database
  8. Dim tableRecord As DAO.Recordset
  9. Dim queryTableData As String
  10. Dim strPROJECT_ID As String
  11. Dim strSECONDARY_APPLICATION As String
  12. Dim strABBREVIATED_NAME As String
  13.  
  14.  
  15. Set db = CurrentDb()
  16.  
  17. queryTableData = "SELECT * FROM DV_SECONDARY_APPLICATION"
  18. Set tableRecord = db.OpenRecordset(queryTableData, dbOpenSnapshot)
  19.  
  20. If Not tableRecord.BOF And Not tableRecord.EOF Then
  21.  
  22.   tableRecord.MoveFirst
  23.  
  24.  
  25.  
  26.   strPROJECT_ID = tableRecord!PROJECT_ID
  27.   strSECONDARY_APPLICATION = tableRecord!SECONDARY_APPLICATION
  28.   strABBREVIATED_NAME = tableRecord!ABBREVIATED_NAME
  29.  
  30.   tableRecord.MoveNext
  31.  
  32.   Do Until tableRecord.EOF
  33.  
  34.     If strPROJECT_ID = tableRecord!PROJECT_ID Then
  35.  
  36.         If strABBREVIATED_NAME <> Null Then
  37.             strABBREVIATED_NAME = strABBREVIATED_NAME & "," & tableRecord!ABBREVIATED_NAME
  38.         Else
  39.             strABBREVIATED_NAME = tableRecord!SECONDARY_APPLICATION
  40.         End If
  41.  
  42.         Else
  43.  
  44.         queryTableData = "INSERT INTO DV_ ABBR_SECONDARY_APPLICATIONS (PROJECT_ID, ADDITIONAL_APPLICATIONS" & "VALUES(&strPROJECT_ID,       &strABBREVIATED_NAME )"
  45.  
  46.             db.Execute queryTableData
  47.          tableRecord.MoveNext
  48.     End If
  49.   Loop
  50.  
  51. End If
  52.  
  53. Set tableRecord = Nothing
  54. Set db = Nothing
  55.  
  56. End Sub
Any help is greatly appreciated..
Apr 10 '14 #3
zmbd
5,501 Expert Mod 4TB
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
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub CreateAddAppsTableCSVList()
  5. On Error Resume Next
  6. '
  7. Dim zdb As DAO.Database
  8. Dim zrsoutsideloop As DAO.Recordset
  9. Dim zrsinsideloop As DAO.Recordset
  10. Dim zrsinsideloopfiltered As DAO.Recordset
  11. Dim zrsholdingtable As DAO.Recordset
  12. '
  13. Dim zSQL As String
  14. '
  15. Dim zX As Integer
  16. '
  17. Set zdb = CurrentDb()
  18. '
  19. '
  20. On Error GoTo zonerror
  21. 'Open recordset to the holding table
  22. zSQL = "SELECT tbl_hold.project_id" & _
  23.         ", tbl_hold.concatenated_Name" & _
  24.         "FROM tbl_hold;"
  25. 'We'll need to add records to this so dynaset
  26. Set zrsholdingtable = zdb.OpenRecordset(zSQL, dbOpenDynaset)
  27. '
  28. 'Open recordset for the outside loop
  29. zSQL = "SELECT DV_Secondary_Application.project_id" & _
  30.         "FROM DV_Secondary_Application" & _
  31.         "GROUP BY DV_Secondary_Application.project_id;"
  32. '
  33. 'if there are any records, we're only go go forward so forwardonly
  34. Set zrsoutsideloop = zdb.OpenRecordset(zSQL, dbOpenForwardOnly)
  35. '
  36. 'if there is any record in the outside loop then feed to the inside loop
  37. If zrsoutsideloop.RecordCount Then
  38.     Do
  39.         'open the recordset to the inside loop using the [zrsoutsideloop]![project_id] in the where clause
  40.         'filter the inside recordset on the abbreviated_name field using the rs.filter method
  41.         'if there are records in the filtered set then concatenate records to string
  42.         'else concatenate the secondary_application field record entries
  43.         '... hint there's a second do until loop in here.
  44.         'add a new record to zrsholdingtable pulling the [zrsoutsideloop]![project_id] and the concatenate to string
  45.        'hint: zrshold.add method
  46. '
  47.     Loop Until zrsoutsideloop.EOF
  48. End If
  49. '
  50. '
  51. zcloseandclean:
  52. If Not zrsoutsideloop Is Nothing Then
  53.     zrsoutsideloop.Close
  54.     Set zrsoutsideloop = Nothing
  55. End If
  56. '
  57. If Not zrsinsideloop Is Nothing Then
  58.     zrsinsideloop.Close
  59.     Set zrsinsideloop = Nothing
  60. End If
  61. '
  62. If Not zrsinsideloopfiltered Is Nothing Then
  63.     zrsinsideloopfiltered.Close
  64.     Set zrsinsideloopfiltered = Nothing
  65. End If
  66. '
  67. If Not zrsholdingtable Is Nothing Then
  68.     zrsholdingtable.Close
  69.     Set zrsholdingtable = Nothing
  70. End If
  71. '
  72. 'exit sub
  73. zonerror:
  74. MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & Err.Source
  75. Resume zcloseandclean
  76. End Sub
  77.  
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
Apr 10 '14 #4
I have worked on this one and got the output now.. Thanks a lot for your help :)
Apr 11 '14 #5
zmbd
5,501 Expert Mod 4TB
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_Name] text(255)

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub CreateAddAppsTableCSVList()
  5. On Error Resume Next
  6. '
  7. Dim zdb As DAO.Database
  8. Dim zrsoutsideloop As DAO.Recordset
  9. Dim zrsinsideloop As DAO.Recordset
  10. Dim zrsinsideloopfiltered As DAO.Recordset Dim zrsholdingtable As DAO.Recordset '
  11. Dim zSQL As String
  12. '
  13. Dim zX As Integer
  14. '
  15. Set zdb = CurrentDb()
  16. '
  17. '
  18. On Error GoTo zonerror
  19. 'Open recordset to the holding table
  20. zSQL = "SELECT tbl_hold.project_id" & _
  21.         ", tbl_hold.concatenated_Name" & _
  22.         " FROM tbl_hold;"
  23. 'We'll need to add records to this so dynaset Set zrsholdingtable = zdb.OpenRecordset(zSQL, dbOpenDynaset) With zrsholdingtable
  24.     If .RecordCount Then
  25.         .MoveLast
  26.         .MoveFirst
  27.         MsgBox "Current Holding Table Count: " & zrsholdingtable.RecordCount
  28.         .MoveLast
  29.     End If
  30. End With
  31. '
  32. '
  33. 'Open recordset for the outside loop
  34. zSQL = "SELECT DV_Secondary_Application.project_id" & _
  35.         " FROM DV_Secondary_Application" & _
  36.         " GROUP BY DV_Secondary_Application.project_id;"
  37. '
  38. 'if there are any records, we're only go go forward so forwardonly Set zrsoutsideloop = zdb.OpenRecordset(zSQL, dbOpenForwardOnly) '
  39. 'if there is any record in the outside loop then feed to the inside loop If zrsoutsideloop.RecordCount Then
  40.     Do
  41.         'open the recordset to the inside loop using the [zrsoutsideloop]![project_id] in the where clause
  42.         zSQL = "SELECT DV_Secondary_Application.Secondary_Application" & _
  43.             ", DV_Secondary_Application.Abbreviated_Name" & _
  44.             " FROM DV_Secondary_Application" & _
  45.             " WHERE (((DV_Secondary_Application.project_id)=" & zrsoutsideloop![project_id] & "));"
  46.         Set zrsinsideloop = zdb.OpenRecordset(zSQL, dbOpenDynaset)
  47.         'filter the inside recordset on the abbreviated_name field using the rs.filter method
  48.         zSQL = "SELECT DV_Secondary_Application.Secondary_Application" & _
  49.             ", DV_Secondary_Application.Abbreviated_Name FROM DV_Secondary_Application" & _
  50.             " WHERE(" & _
  51.                 " (DV_Secondary_Application.project_id=" & zrsoutsideloop![project_id] & ")" & _
  52.                 " AND" & _
  53.                     "((DV_Secondary_Application.Abbreviated_Name) Is Not Null)" & _
  54.                 " OR" & _
  55.                     " (DV_Secondary_Application.Abbreviated_Name)='');"
  56.         Debug.Print zSQL
  57.         Set zrsinsideloopfiltered = zdb.OpenRecordset(zSQL, dbOpenDynaset)
  58.         'if there are records in the filtered set then concatenate records to string
  59.         zSQL = ""
  60.         If zrsinsideloopfiltered.RecordCount Then
  61.             With zrsinsideloopfiltered
  62.                 .MoveLast
  63.                 .MoveFirst
  64.                 Do
  65.                     zSQL = zSQL & ![Abbreviated_Name] & ","
  66.                     .MoveNext
  67.                 Loop Until .EOF
  68.             End With
  69.         Else
  70.             With zrsinsideloop
  71.                 .MoveLast
  72.                 .MoveFirst
  73.                 Do
  74.                     zSQL = zSQL & ![Secondary_Application] & ","
  75.                     .MoveNext
  76.                 Loop Until .EOF
  77.             End With
  78.         End If
  79.         'remove trailing comma
  80.         zSQL = Left(zSQL, (Len(zSQL) - 1))
  81.         'add a new record to zrsholdingtable pulling the [zrsoutsideloop]![project_id] and the concatenate to string
  82.         With zrsholdingtable
  83.             .AddNew
  84.             !project_id = zrsoutsideloop![project_id]
  85.             !concatenated_Name = zSQL
  86.             .Update
  87.         End With
  88.         zrsoutsideloop.MoveNext
  89.     Loop Until zrsoutsideloop.EOF
  90. End If
  91. '
  92. MsgBox "Current Holding Table Count: " & zrsholdingtable.RecordCount '
  93. '
  94. zcloseandclean:
  95. If Not zrsoutsideloop Is Nothing Then
  96.     zrsoutsideloop.Close
  97.     Set zrsoutsideloop = Nothing
  98. End If
  99. '
  100. If Not zrsinsideloop Is Nothing Then
  101.     zrsinsideloop.Close
  102.     Set zrsinsideloop = Nothing
  103. End If
  104. '
  105. If Not zrsinsideloopfiltered Is Nothing Then
  106.     zrsinsideloopfiltered.Close
  107.     Set zrsinsideloopfiltered = Nothing
  108. End If
  109. '
  110. If Not zrsholdingtable Is Nothing Then
  111.     zrsholdingtable.Close
  112.     Set zrsholdingtable = Nothing
  113. End If
  114. '
  115. Exit Sub
  116. zonerror:
  117. MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & Err.Source Resume zcloseandclean
  118. End Sub
  119.  
Apr 11 '14 #6
Thanks for the code guys it worked like wonder.

@zmbd
Apr 16 '15 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Andla Rand | last post by:
Hi, How to add new columns using an ArrayList without creating a class ? Notice: bc.DataField="!" works when AutoGenerateColumns is off. Otherwise nothing is displayed. ArrayList a=new...
5
by: Andrei Pociu | last post by:
I have a major doubt about outputting text in ASP .NET when using code behind. I know most of the output you gain from a code behind file (.aspx.cs) is outputted to the Webform (.aspx) using...
5
by: Victor Reboucas | last post by:
Hi, I'm sure I'm missing something here, but.... I have a asp.net 2.0 web form and a calendar control. If I set the selectedDate clicking the control, no problem, but, if I set it using code I...
7
by: ApexData | last post by:
Hello I currently Link the FE/BE using the LinkTables Option and the Linked Table Manager. Any time I need to move the BE to another location, I have to go through this process over again. I...
8
by: Greg (codepug | last post by:
For lack of the proper expression, how do I excite a control to cause events to trigger. I have a date control and am using a calender form button to fill the text box with the date. The text box...
4
Haitashi
by: Haitashi | last post by:
Hi: I need to dymanically create a number of columns using the queryAddColumn function. I was going to use the loop below. That way each column would have a generic dynamically created name. ...
7
Alireza355
by: Alireza355 | last post by:
Dear all, Is there a way I can update all of the numbers in a certain column of a certain table that are <0 to NULL using access VBA code? Thanx a lot
0
by: Orbie | last post by:
Hi Guys, I need some help with pivoting or converting some rows on a Table into columns using SQL Server 2008! I have a Table which contains the same Products in 4 different Stores. I'm only...
1
by: santhanalakshmi | last post by:
Hi, I uploaded the excel and the data is successfully inserted to the database, by submitting the "submit button" using php script. There is an final stage for me to do is the validation of...
4
by: Joe Y | last post by:
I'm using an Allen Brown concatenate related records code from Allen Browne (http://allenbrowne.com/func-concat.html). The code works fine when I have only one criterion. The code is like this:...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.