Combine multiple data into in field

wongray · Mar 20, 2010

Hi,

I need help, I have a database that I need to combine all the country into one single field base on an ID.

Please advice how to write a module to do this automatically, where I can remove the combine data, then append with the latest data..

here is the attached sample

Thanks
Ray

Khalid_Afridi · Mar 20, 2010

you mean you want to put all the countries in a single field (Combine_Country) of 'Tb_Country_Combine' where Project_ID is same in your 'table_country table' ?

Project_ID Country
1 Hong Kong
1 Singapore
2 Hong Kong
3 Singapore
3 Thailand
4 Singapore
5 Singapore
6 Hong Kong
7 Japan
7 Hong Kong

wongray · Mar 20, 2010

Yes, so the results will be :

project_ID
1

country
hong kong , Singapore

many thakns

vbaInet · Mar 20, 2010

Here's a good resource:
http://allenbrowne.com/func-concat.html

Khalid_Afridi · Mar 20, 2010

This can be acheived with the following code:

Code:

Sub InsertInSingleField()
 
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Set db = CurrentDb
strSQL = "Select Project_ID, Country From Tbl_Country"
Set rs = db.OpenRecordset(strSQL)
 
Dim cmbineField As String
Dim p_id As Integer
 
p_id = rs!Project_ID
Do While Not rs.EOF
 
      If p_id = rs!Project_ID Then
          txtCountry = rs!country
          p_id = rs!Project_ID
          cmbineField = cmbineField & "," & txtCountry
          rs.MoveNext
          Debug.Print cmbineField
     Else
         Debug.Print rs!country
         p_id = rs!Project_ID
         rs.MoveNext
End If
Loop
 
End Sub

but still I have to do some more work to finish...

wongray · Mar 20, 2010

Hi Afridi,

THanks, but I'm very new on this setup.. where should I put the code ?

Can you help me to put it into my sample data?

appreciated

Khalid_Afridi · Mar 21, 2010

Hi wongray!

create a button on a form, and then on click event of the button just copy and paste the following code in VBA module:

Code:

Private Sub Command1_Click()
 
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Set db = CurrentDb
strSQL = "Select Project_ID, Country From Tbl_Country"
Set rs = db.OpenRecordset(strSQL)
 
Dim txtCountry As String
Dim p_id As Long
 
Do While Not rs.EOF
 
    p_id = rs!Project_ID
 
    txtCountry = rs!country
    Debug.Print rs!Project_ID & ":" & txtCountry [COLOR=seagreen]'change this Line with INSERT Statement to insert the new record.[/COLOR]    
    rs.MoveNext
 
    Do While p_id = rs!Project_ID
          txtCountry = txtCountry & "," & rs!country
          Debug.Print rs!Project_ID & ":" & txtCountry [COLOR=seagreen]'change this Line with UPDATE Statement to update the record.[/COLOR]
          p_id = rs!Project_ID
          rs.MoveNext
          If rs.EOF Then Exit Sub
      Loop
Loop
End Sub

Watch the result in immediate window of VBA, it will produce the following text:

1:Hong Kong
1:Hong Kong,Singapore
2:Hong Kong
3:Singapore
3:Singapore,Thailand
4:Singapore
5:Singapore
6:Hong Kong
7:Japan
7:Japan,Hong Kong

if you know how to insert and update tables using VBA then change the
debug.print line with INSERT and UPDATE command in VBA code to insert and update your desire table.

Khalid

wongray · Apr 3, 2010

vbaInet · Apr 3, 2010

If you had read what was in post #4 you would have found an answer. That's a very common source for concatenating related records.

wongray · Apr 3, 2010

Hi VbaInet,

Thanks a lot for the info, it is possible for you to show me on my sample? I'm pretty new at this and if you could do it under my sample data, that would be great..

Still learning a lot here..

Many thanks

Wongray

vbaInet · Apr 3, 2010

Wongray, the way to learn is to do it. The link has a step-by-step guide how to do it. Not hard at all. Give it a go, read it step-by-step.

vbaInet · Apr 3, 2010

Hmmm... wongray, I had a look at your table and I noticed it wasn't a matter of just using the link I gave you. You needed to return distinct countries first for it to work.

I have implemented:

1. A subquery to return the first country per Project_ID (look at the sql view and the design view of qryCountryALL). In this query I also used the concat related function provided in the link by Allen Browne
2. A query based on qryCountryALL to get the distinct rows (look at the sql view and the design view of qryDISTINCTCountry)

Khalid_Afridi · Apr 4, 2010

Hi wongray!

If you study the code I already posted earliar, that works exactly your requirement, only you have to add a code for Inserting and updating your new table "tb_Country_Combine".

Anyhow, I added that one also just to insert and update your table:
Here is the complete code:

Code:

Sub InsertInSingleField()
 
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Set db = CurrentDb
strSQL = "Select Project_ID, Country From Tbl_Country"
Set rs = db.OpenRecordset(strSQL)
 
Dim txtCountry As String
Dim p_id As Long
 
Do While Not rs.EOF
 
    p_id = rs!Project_ID
    txtCountry = rs!country
 
    Debug.Print rs!Project_ID & ":" & txtCountry 'change this Line with INSERT Statement to insert the new record.
 
    strSQL = "INSERT INTO tb_Country_Combine (Project_ID, Combine_Country)"
    strSQL = strSQL + "VALUES ("
    strSQL = strSQL + "'" & rs!Project_ID & "', "
    strSQL = strSQL + "'" & txtCountry & "' )"
    CurrentDb.Execute (strSQL)
 
    rs.MoveNext
 
    Do While p_id = rs!Project_ID
          txtCountry = txtCountry & "," & rs!country
          Debug.Print rs!Project_ID & ":" & txtCountry 'change this Line with UPDATE Statement to update the record.
          strSQL = "UPDATE tb_Country_Combine SET tb_Country_Combine.Combine_Country = '" & txtCountry & "'"
          strSQL = strSQL & " WHERE tb_Country_Combine.Project_ID=" & rs!Project_ID
          CurrentDb.Execute (strSQL)
 
          p_id = rs!Project_ID
          rs.MoveNext
          If rs.EOF Then Exit Sub
      Loop
Loop
 
End Sub

What this code do:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
strSQL = "Select Project_ID, Country From Tbl_Country"
Set rs = db.OpenRecordset(strSQL)

Declare variables and opent the recordset

Dim txtCountry As String
Dim p_id As Long

Do While Not rs.EOF

p_id = rs!Project_ID

txtCountry = rs!country

Debug.Print rs!Project_ID & ":" & txtCountry 'change this Line with INSERT Statement to insert the new record.

Loop through recodeset till end of file and store the Project_ID and Country name in variables, and get the result in immediat window.

strSQL = "INSERT INTO tb_Country_Combine (Project_ID, Combine_Country)"
strSQL = strSQL + "VALUES ("
strSQL = strSQL + "'" & rs!Project_ID & "', "
strSQL = strSQL + "'" & txtCountry & "' )"
CurrentDb.Execute (strSQL)

rs.MoveNext

Here it inserts the Project_ID and Country to your new table "tb_Country_Combine "

Do While p_id = rs!Project_ID
txtCountry = txtCountry & "," & rs!country
Debug.Print rs!Project_ID & ":" & txtCountry 'change this Line with UPDATE Statement to update the record.
strSQL = "UPDATE tb_Country_Combine SET tb_Country_Combine.Combine_Country = '" & txtCountry & "'"
strSQL = strSQL & " WHERE tb_Country_Combine.Project_ID=" & rs!Project_ID
CurrentDb.Execute (strSQL)

here it update your "tb_Country_Combine" Where Project_ID is same.

your table is having the following resuts:

Project_ID Country
1 Hong Kong
1 Singapore
2 Hong Kong
3 Singapore
3 Thailand
4 Singapore
5 Singapore
6 Hong Kong
7 Japan
7 Hong Kong

Your "tb_Country_Combine" is having now the following results:

Project_ID Combine_Country
1 Hong Kong,Singapore
2 Hong Kong
3 Singapore,Thailand
4 Singapore
5 Singapore
6 Hong Kong
7 Japan,Hong Kong

Your db is attached back to the post with the code in Module1 as per your requirement

Cheers!

wongray · Apr 4, 2010

Hi Khalid Afridi.

THanks a lot for showing me the example. I got it to work.. but when I try to do it and run in a bigger data.. I got this error.

Run-time error '3021' :
no current record.

I did search in the net and said, that I can write a trap on it.. but don't where should I put the trap on.

can you please advice?

many Thanks
wongray

vbaInet · Apr 5, 2010

You don't store calculated fields wongray. It seems to me that you want to use that data for a report or a query. Am I correct in my thinking? The database I provided in post #12 does just that. Every db expert on this forum would advise the same that you don't store these values.

However, there are certain circumstances where storing the values is essential. If your case is one of those then you can use the query in my last post to insert into a new table. Here's a link on how to INSERT using another query:

http://www.techonthenet.com/sql/insert.php

It's up to you what you do now. Good luck.

Khalid_Afridi · Apr 5, 2010

wongray said:
Hi Khalid Afridi.

THanks a lot for showing me the example. I got it to work.. but when I try to do it and run in a bigger data.. I got this error.

Run-time error '3021' :
no current record.

I did search in the net and said, that I can write a trap on it.. but don't where should I put the trap on.

can you please advice?

many Thanks
wongray

Its seems that your table has no record, its empty, to stop running the code if your recordset is blank i.e there is no record in the source table. Just add the following line in the code I wrote:

Code:

If rs.RecordCount > 0 Then ' It check if your table has some records in it.
...
...
...
End If

Code:

Sub InsertInSingleField()
 
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Set db = CurrentDb
strSQL = "Select Project_ID, Country From Tbl_Country"
Set rs = db.OpenRecordset(strSQL)
 
Dim txtCountry As String
Dim p_id As Long
[COLOR="Red"]If rs.RecordCount > 0 Then ' It check if your table has some records in it[/COLOR] 
Do While Not rs.EOF
 
    p_id = rs!Project_ID
    txtCountry = rs!country
 
    Debug.Print rs!Project_ID & ":" & txtCountry 'change this Line with INSERT Statement to insert the new record.
 
    strSQL = "INSERT INTO tb_Country_Combine (Project_ID, Combine_Country)"
    strSQL = strSQL + "VALUES ("
    strSQL = strSQL + "'" & rs!Project_ID & "', "
    strSQL = strSQL + "'" & txtCountry & "' )"
    CurrentDb.Execute (strSQL)
 
    rs.MoveNext
 
    Do While p_id = rs!Project_ID
          txtCountry = txtCountry & "," & rs!country
          Debug.Print rs!Project_ID & ":" & txtCountry 'change this Line with UPDATE Statement to update the record.
          strSQL = "UPDATE tb_Country_Combine SET tb_Country_Combine.Combine_Country = '" & txtCountry & "'"
          strSQL = strSQL & " WHERE tb_Country_Combine.Project_ID=" & rs!Project_ID
          CurrentDb.Execute (strSQL)
 
          p_id = rs!Project_ID
          rs.MoveNext
          If rs.EOF Then Exit Sub
      Loop
Loop
[COLOR="red"]End If [/COLOR]
End Sub
What this code do:

Remember! you have to check the tables names/Fields names, the above code is based on your db of your first post, (If you change your tables name,field names, then you should change the Field/tables names also in the code.
Also check the field size for storing your countries data for Hong Kong,Singapore etc. should be at least capable to hold all the data (Max 255) characters, in other case you will loss some countries names to store.

Hope this make sense.

wongray · Apr 6, 2010

Hi vbaInet,

Many Many Thanks for your advice.. The sample that you given works exactly what I need.

apprecaited.

regards
wongray

vbaInet · Apr 6, 2010

You're welcome. Always bear in mind, if you calculate it, don't store it. Only in special circumstances would you need to save the values

Happy to have helped.

Combine multiple data into in field

Registered User.

Attachments

Registered User.

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

AWF VIP

Attachments

Registered User.

Attachments

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread