By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,453 Members | 932 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,453 IT Pros & Developers. It's quick & easy.

Newbie trying to Concatenate

P: 4
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!
Feb 25 '08 #1
Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,701
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.
Feb 25 '08 #2

ADezii
Expert 5K+
P: 8,701
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]:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fConcatenateSubnets(strOrg As String) As String
    2. Dim MyDB As DAO.database, MyRS As DAO.Recordset
    3. Dim strBuildString As String
    4.  
    5. Set MyDB = CurrentDb
    6. Set MyRS = MyDB.OpenRecordset("tblTest", dbOpenForwardOnly)
    7.  
    8. strBuildString = ""     'initialize
    9.  
    10. Do While Not MyRS.EOF
    11.   If MyRS![Org] = strOrg Then
    12.     strBuildString = strBuildString & MyRS![Subnet] & ", "
    13.   End If
    14.     MyRS.MoveNext
    15. Loop
    16.  
    17. 'Strip out the last Space and , ==> namely "' "
    18. fConcatenateSubnets = Left$(strBuildString, Len(strBuildString) - 2)
    19.  
    20. MyRS.Close:Set MyRS = Nothing
    21. End Function
  2. Query SQL Statement:
    Expand|Select|Wrap|Line Numbers
    1. SELECT DISTINCT tblTest.Org, fConcatenateSubnets([Org]) AS All_Subnets
    2. FROM tblTest
    3. ORDER BY tblTest.Org;
  3. Output based on your data:
    Expand|Select|Wrap|Line Numbers
    1. Org           All_Subnets
    2. Rogers       10.10.1.0/81, 10.10.1.0/82
    3. Shaw       10.10.1.0/24, 10.10.1.0/25, 10.10.1.0/38
    4. 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.
Feb 25 '08 #3

P: 4
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]:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fConcatenateSubnets(strOrg As String) As String
    2. Dim MyDB As DAO.database, MyRS As DAO.Recordset
    3. Dim strBuildString As String
    4.  
    5. Set MyDB = CurrentDb
    6. Set MyRS = MyDB.OpenRecordset("tblTest", dbOpenForwardOnly)
    7.  
    8. strBuildString = ""     'initialize
    9.  
    10. Do While Not MyRS.EOF
    11.   If MyRS![Org] = strOrg Then
    12.     strBuildString = strBuildString & MyRS![Subnet] & ", "
    13.   End If
    14.     MyRS.MoveNext
    15. Loop
    16.  
    17. 'Strip out the last Space and , ==> namely "' "
    18. fConcatenateSubnets = Left$(strBuildString, Len(strBuildString) - 2)
    19.  
    20. MyRS.Close:Set MyRS = Nothing
    21. End Function
  2. Query SQL Statement:
    Expand|Select|Wrap|Line Numbers
    1. SELECT DISTINCT tblTest.Org, fConcatenateSubnets([Org]) AS All_Subnets
    2. FROM tblTest
    3. ORDER BY tblTest.Org;
  3. Output based on your data:
    Expand|Select|Wrap|Line Numbers
    1. Org           All_Subnets
    2. Rogers       10.10.1.0/81, 10.10.1.0/82
    3. Shaw       10.10.1.0/24, 10.10.1.0/25, 10.10.1.0/38
    4. 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.
Feb 26 '08 #4

ADezii
Expert 5K+
P: 8,701
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function fConcatenateSubnets(strOrg As String) As String
Feb 26 '08 #5

NeoPa
Expert Mod 15k+
P: 31,768
Producing a List from Multiple Records may help with this (as an alternative).
Feb 26 '08 #6

P: 4
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.......;-)
Feb 27 '08 #7

ADezii
Expert 5K+
P: 8,701
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).
Feb 27 '08 #8

NeoPa
Expert Mod 15k+
P: 31,768
Well done :)
Are the versions of Access both the same (at home and at work)?
Feb 27 '08 #9

P: 4
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?
Feb 28 '08 #10

NeoPa
Expert Mod 15k+
P: 31,768
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 :(
Feb 28 '08 #11

Post your reply

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