Newbie trying to Concatenate

**ADezii** · Feb 25 '08, 03:14 AM

Originally posted by bingus

Hello,
Hopefully this is a simple problem that someone can help me with easily. I have one table with two fields. The first field contains a subnet_org and the second contains a subnet. The first column has many duplicates because some "orgs" have numerous subnets....whic h are all listed in the second field in inidividual rows.
Org Subnets
Rogers 10.10.1.0/81
Rogers 10.10.1.0/82
Shaw 10.10.1.0/24
Shaw 10.10.1.0/25
Shaw 10.10.1.0/38
Telus 10.10.1.0/39
Telus 10.10.1.0/40
Telus 10.10.1.0/41
Telus 10.10.1.0/42
Telus 10.10.1.0/43

I need to run a query whose output concatenates the subnet for each org....so the output looks thusly:
Telus 10.10.1.0/39, 10.10.1.0/40, 0.10.1.0/41....etc
with one row for each org.

Thanks in advance for any help anyone can offer.
Cheers!

I'll have an answer for you shortly, last one before I go tp bed.

**ADezii** · Feb 25 '08, 03:40 AM

Originally posted by bingus

Hello,
Hopefully this is a simple problem that someone can help me with easily. I have one table with two fields. The first field contains a subnet_org and the second contains a subnet. The first column has many duplicates because some "orgs" have numerous subnets....whic h are all listed in the second field in inidividual rows.
Org Subnets
Rogers 10.10.1.0/81
Rogers 10.10.1.0/82
Shaw 10.10.1.0/24
Shaw 10.10.1.0/25
Shaw 10.10.1.0/38
Telus 10.10.1.0/39
Telus 10.10.1.0/40
Telus 10.10.1.0/41
Telus 10.10.1.0/42
Telus 10.10.1.0/43

I need to run a query whose output concatenates the subnet for each org....so the output looks thusly:
Telus 10.10.1.0/39, 10.10.1.0/40, 0.10.1.0/41....etc
with one row for each org.

Thanks in advance for any help anyone can offer.
Cheers!

OK, I'm back, but first three simple Assumptions:

Table Name is tblTest
Field 1 Name is [Org]
Field 2 Name is [Subnet]

Function that does all the work. It is called by the Calculated Field 'All_Subnets' in the Query for each unique [Org]:
[CODE=vb]
Public Function fConcatenateSub nets(strOrg As String) As String
Dim MyDB As DAO.database, MyRS As DAO.Recordset
Dim strBuildString As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecord set("tblTest", dbOpenForwardOn ly)

strBuildString = "" 'initialize

Do While Not MyRS.EOF
If MyRS![Org] = strOrg Then
strBuildString = strBuildString & MyRS![Subnet] & ", "
End If
MyRS.MoveNext
Loop

'Strip out the last Space and , ==> namely "' "
fConcatenateSub nets = Left$(strBuildS tring, Len(strBuildStr ing) - 2)

MyRS.Close:Set MyRS = Nothing
End Function[/CODE]
Query SQL Statement:
[CODE=vb]
SELECT DISTINCT tblTest.Org, fConcatenateSub nets([Org]) AS All_Subnets
FROM tblTest
ORDER BY tblTest.Org;[/CODE]
Output based on your data:
[CODE=text]
Org All_Subnets
Rogers 10.10.1.0/81, 10.10.1.0/82
Shaw 10.10.1.0/24, 10.10.1.0/25, 10.10.1.0/38
Telus 10.10.1.0/39, 10.10.1.0/40, 10.10.1.0/41, 10.10.1.0/42, 10.10.1.0/43[/CODE]
Any questions, please feel free to ask.

**bingus** · Feb 26 '08, 04:43 AM

ADezii,

Thank you so much for your quick response. I'm not only new to VBA but also Access. I'm sure your code will work marvelously once I figure out the issues I'm having with Access.

I keep getting a "function is undefined" message when I try and run the query. I know it's something I'm doing, or not doing, because I can run sample queries that call functions....li ke Northwind type stuff........bu t even if I recreate the exact same table, function then query as the Northwind sample...I still get this error. But when I call the Northwind function in my own query against my own table....it works fine....go figure!

Anyway, thanks again.....I'll keep plugging away!!

Originally posted by ADezii

OK, I'm back, but first three simple Assumptions:

Table Name is tblTest
Field 1 Name is [Org]
Field 2 Name is [Subnet]

Function that does all the work. It is called by the Calculated Field 'All_Subnets' in the Query for each unique [Org]:
[CODE=vb]
Public Function fConcatenateSub nets(strOrg As String) As String
Dim MyDB As DAO.database, MyRS As DAO.Recordset
Dim strBuildString As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecord set("tblTest", dbOpenForwardOn ly)

strBuildString = "" 'initialize

Do While Not MyRS.EOF
If MyRS![Org] = strOrg Then
strBuildString = strBuildString & MyRS![Subnet] & ", "
End If
MyRS.MoveNext
Loop

'Strip out the last Space and , ==> namely "' "
fConcatenateSub nets = Left$(strBuildS tring, Len(strBuildStr ing) - 2)

MyRS.Close:Set MyRS = Nothing
End Function[/CODE]
Query SQL Statement:
[CODE=vb]
SELECT DISTINCT tblTest.Org, fConcatenateSub nets([Org]) AS All_Subnets
FROM tblTest
ORDER BY tblTest.Org;[/CODE]
Output based on your data:
[CODE=text]
Org All_Subnets
Rogers 10.10.1.0/81, 10.10.1.0/82
Shaw 10.10.1.0/24, 10.10.1.0/25, 10.10.1.0/38
Telus 10.10.1.0/39, 10.10.1.0/40, 10.10.1.0/41, 10.10.1.0/42, 10.10.1.0/43[/CODE]
Any questions, please feel free to ask.

**ADezii** · Feb 26 '08, 01:34 PM

Originally posted by bingus

ADezii,

Thank you so much for your quick response. I'm not only new to VBA but also Access. I'm sure your code will work marvelously once I figure out the issues I'm having with Access.

I keep getting a "function is undefined" message when I try and run the query. I know it's something I'm doing, or not doing, because I can run sample queries that call functions....li ke Northwind type stuff........bu t even if I recreate the exact same table, function then query as the Northwind sample...I still get this error. But when I call the Northwind function in my own query against my own table....it works fine....go figure!

Anyway, thanks again.....I'll keep plugging away!!

The Function must be declared as 'Public' in a 'Standard' Code Module, That is probably why you are getting the Error:
[CODE=vb]Public Function fConcatenateSub nets(strOrg As String) As String[/CODE]

**NeoPa** · Feb 26 '08, 02:08 PM

Producing a List from Multiple Records may help with this (as an alternative).

**bingus** · Feb 27 '08, 05:50 AM

Thanks again ADezii and NeoPa!!

I've been trying to implement your code at my office and have not been successful. Tonight is the first night I've tried this at home and it works!! I don't know what is different .....but it doesn't matter because I know I can get the job done.

I really appreciate all your help.

Thanks again.......;-)

**ADezii** · Feb 27 '08, 01:11 PM

Originally posted by bingus

Thanks again ADezii and NeoPa!!

I've been trying to implement your code at my office and have not been successful. Tonight is the first night I've tried this at home and it works!! I don't know what is different .....but it doesn't matter because I know I can get the job done.

I really appreciate all your help.

Thanks again.......;-)

That is why we are all here, it definately is not the salary! (LOL).

**NeoPa** · Feb 27 '08, 02:11 PM

Well done :)
Are the versions of Access both the same (at home and at work)?

**bingus** · Feb 28 '08, 12:53 AM

Almost out of the woods!!
How can I change the format of the All_Subnets field to "memo" .....if that is what is required.

My issue, now, is that some of my "Orgs" have over 300 subnets and I've found some of the "All_Subnet s" fields have truncated the data and contain the maximum 255 characters of a text field only. Fortunately of the 200 odd "Orgs" I have only about 10% of them present me with this issue.

Any ideas on this one?

**NeoPa** · Feb 28 '08, 01:05 AM

I'm not sure there is a way. I tried to force something similar to this using the PARAMETERS clause earlier, but I found it wouldn't work as I intended. I doubt it'll be much use to you either. Sorry :(

Newbie trying to Concatenate

Newbie trying to Concatenate

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment