Newbie trying to Concatenate

[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....which 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!

[ADezii]

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....which 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]

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....which 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:

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

1. Function that does all the work. It is called by the Calculated Field 'All_Subnets' in the Query for each unique [Org]:
   

   Public Function fConcatenateSubnets(strOrg As String) As String
   Dim MyDB As DAO.database, MyRS As DAO.Recordset
   Dim strBuildString As String
    
   Set MyDB = CurrentDb
   Set MyRS = MyDB.OpenRecordset("tblTest", dbOpenForwardOnly)
    
   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 "' "
   fConcatenateSubnets = Left$(strBuildString, Len(strBuildString) - 2)
    
   MyRS.Close:Set MyRS = Nothing
   End Function

2. Query SQL Statement:
   

   SELECT DISTINCT tblTest.Org, fConcatenateSubnets([Org]) AS All_Subnets
   FROM tblTest
   ORDER BY tblTest.Org;

3. Output based on your data:
   

   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

4. Any questions, please feel free to ask.

[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....like Northwind type stuff........but 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!!

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

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

1. Function that does all the work. It is called by the Calculated Field 'All_Subnets' in the Query for each unique [Org]:
   

   Public Function fConcatenateSubnets(strOrg As String) As String
   Dim MyDB As DAO.database, MyRS As DAO.Recordset
   Dim strBuildString As String
    
   Set MyDB = CurrentDb
   Set MyRS = MyDB.OpenRecordset("tblTest", dbOpenForwardOnly)
    
   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 "' "
   fConcatenateSubnets = Left$(strBuildString, Len(strBuildString) - 2)
    
   MyRS.Close:Set MyRS = Nothing
   End Function

2. Query SQL Statement:
   

   SELECT DISTINCT tblTest.Org, fConcatenateSubnets([Org]) AS All_Subnets
   FROM tblTest
   ORDER BY tblTest.Org;

3. Output based on your data:
   

   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

4. Any questions, please feel free to ask.

[ADezii]

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....like Northwind type stuff........but 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:

Public Function fConcatenateSubnets(strOrg As String) As String

[NeoPa]

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

[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.......;-)

[ADezii]

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]

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

[bingus]

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_Subnets" 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]

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 :(