Concatenate Strings from different rows in same column grouped by ID 1

[SeanDagger]

Is there an easy way to Concatenate Strings from different rows in the same column grouped by ID? (all in one table).

For Example:
Client ID County
100 Newcastle
100 Cleveland
200 Northumberland
200 County Durham
200 Newcastle
300 Cleveland

convert to:
ID County
100 Newcastle; Cleveland
200 Northumberland; County Durham; Newcastle
300 Cleveland

I am only using MS Access to do this, no asp code or anything, so ideally, I would like to do it all as an Access query if possible.

Many thanks for taking the time to read this and any help would be fantastic.

Thanks

Sean.

 

[HarleyQuinn]

Hi,

I know you said preferably no code but this should give you something to go with faq701-3499

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SeanDagger]

Thank you for pointing me in the right direction.

Unfortunately, I'm not the best programmer around and have never done VBA within Access. Am I right that I create a new module to use the code you suggested? How would I use the code once I have created the function?

Sorry if I am asking for an abc on this.

Sean.

 

[HarleyQuinn]

Don't worry, everyone has to ask sometimes or none of us would ever learn anything new

You can put it in a module as a Sub and call it from a form (or a macro).

You'll just have to change the SQL statement and you should be good to go.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SeanDagger]

Finding it difficult to work out what to do.

My details are as follows.

Database: CustomerDataConvert.mdb
Table: DevelopmentLeads
Fields: ClientID and County (both type text).

If I make a module, do I change it to be a function or keep it as a Private Sub Command0_Click()?

then do I make a new query to somehow call it? i'm stuck sorry.

I basically want to concatenate all the counties into one text field (or memo) where the clientIDs are the same.

 

[HarleyQuinn]

OK, lets have a look see what we can do.

Basically 'project_nbr_store_list' is your 'DevelopmentLeads' table, 'project_nbr' is your 'ClientId' field and 'store' is your 'County' field.

You'll also need to create a table called TempTable with both the fields you want to show in (named the same as in your table).

Just to try it out (to make sure you've got the code right), create a form and put a button on called Command0.

Go to the code for the button (Properties-->Event-->OnClick) and click the ellipsees next to it. Paste all of the code in except Private Sub Command0_Click() and
End Sub (as they'll already be there).

Run the form and then click the button, then look in TampTable and see if it's what you want.

Hope this helps


HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SeanDagger]

Many thanks. I reallly appreciate you helping like this.

This is the code I now have in the module, I am tesing it within the module with the debug tool. It is inserting the clientids in the temptable no problems (now I added the v3.6 DAO library). However, it keeps telling me that there is an invalid use of null in vstore variable (some fields will be null so won't be concatenated)?

Private Sub CountyConcat()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim vStore As String ' string to contain list of counties

Set db = CurrentDb

'clear out TempTable
db.Execute "DELETE TempTable.* FROM TempTable;"

'first of all insert list of projects into table TempTable
db.Execute ("insert into TempTable (CLIENT__C) select distinct CLIENT__C from ExternalClientExtract_DevelopmentLeads")

Set rs = db.OpenRecordset("select distinct CLIENT__C from TempTable")
'outer loop containing list of project numbers

Do Until rs.EOF = True
'for each project number create a string of stores
vStore = ""

Dim sql As String
sql = "select COUNTY__C from ExternalClientExtract_DevelopmentLeads where CLIENT__C ='" & rs!CLIENT__C & "'"
'select list of stores for current project number

Set rs1 = db.OpenRecordset(sql)
'inner loop to create a concatenated string of stores

Do While Not rs1.EOF = False
vStore = vStore + ", " + rs1!COUNTY__C
rs1.MoveNext
Loop

If Len(vStore) > 0 Then
'trim off leading comma
vStore = Right(vStore, Len(vStore) - 1)

'update project list with store string
db.Execute ("UPDATE TempTable SET COUNTY__C = '" + vStore + "' WHERE CLIENT__C = '" & rs!CLIENT__C & "'")

Else
'dont bother with update
End If
'move to next project number

rs.MoveNext
Loop

'release the memory
Set rs = Nothing
Set rs1 = Nothing
Set db = Nothing

End Sub

Again, Fantastic help.

 

[SeanDagger]

This is the section of code that is always empty. it is not concatenating or inserting into temptable?

Do While Not rs1.EOF = False
vStore = vStore + ", " + rs1!COUNTY__C
rs1.MoveNext
Loop

 

[HarleyQuinn]

Try something like this (I've guessed the line it errors on):

vStore = vStore + ", " + Nz(rs1!COUNTY__C,"")

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[MakeItSo]

This condition does not make sense.
EOF is already a boolean value; so one should use it:

Do While Not rs1.EOF

Eliminate the =FALSE

Cheers,
miS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SeanDagger]

Wow you are a star. Very almost there. It is inserting the data and concatenating as it should. However it is adding commas even if the field is blank.

for example:
100 Cleveland, , , , , , , , , Newcastle, County Durham
200 Northumberland,
300 Newcastle, Northumberland

some are ok but others not.

Oh I do wish I could program like you guys

Fab help!

 

[HarleyQuinn]

You're right, almost there!

I was going to suggest this but thought you might want the blanks for some reason. Try

If Not IsNull(rs1!COUNTY__C) then vStore = vStore + ", " + rs1!COUNTY__C

That should eliminate the blanks.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SeanDagger]

Also

It seems that I have results such as:

100 Northumberland, Northumberland, Northumberland

How would I make this only appear once?

So sorry for pestering. I'm rubbish at this but i'm learning a hell of a lot.

 

[HarleyQuinn]

No problem at all

Try:

Dim sql As String
    sql = "select [red]DISTINCT[/red] COUNTY__C from ExternalClientExtract_DevelopmentLeads where CLIENT__C ='" & rs!CLIENT__C & "'"

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SeanDagger]

Thank you Thank you Thank you. You are a STAR!!!!

All is working beautifully now thanks to your help. I would have never done this on my own. I have now set up a macro to open the module and perform the code.

Works great. Thank you so much, can't thank you enough.

Bestest regards

Sean.

 

[HarleyQuinn]

You're welcome, I'm glad you got it working

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[MakeItSo]

That's why he's the tip master!
Leading a total vba newbie to a functioning procedure.


Way to go, Harley!

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[HarleyQuinn]

Cheers MakeItSo

The star is appreciated as well

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.