How do I do Text To Columns in Access? 2

[sprite]

I have a table with a list of Business Locations
(BL) and for each BL, there may be multiple Account IDs (AI). For each unique AI, there is a row. It looks something like this:

BL AI
12345 135790
12345 124680
12345 567321
54321 975315
54321 456123

What I need is a table that looks like this:

BL AIs
12345 135790, 124680, 567321
54321 975315, 456123

How do I do this?
Thanks,
Sprite

 

[lespaul]

you'll need to look into looping through the recordset and adding each AI to a string and adding the commas. There's no way to do this in SQL.

Leslie

 

[sprite]

Thanks lespaul, but I don't know where to start with "looping through the recordset...." I'm not good with writing code. Can you help?
Thanks,
Sprite

 

[lespaul]

Something along these lines:

Private Sub CreateAIList_Click()

Dim db As Database
Dim rs As Recordset
Dim strAI As String ' string to contain list of competitors


  Set db = CurrentDb


  [COLOR=red]Set rs = db.OpenRecordset("put your query here")[/color]
  strAI = ""

  Do Until rs.EOF = True
    If strAI = "" then    
      strAI = rs1!AI
    else
      strAI = strAI & ", " & strAI
   rs.MoveNext
   Loop
    
'release the memory
Set rs = Nothing
Set db = Nothing

End Sub

Leslie

 

[sprite]

Thanks Leslie,
I'm getting a "Loop without a Do" error message. Here is the code I have:

Private Sub CreateAIList_Click()

Dim db As Database
Dim rs As Recordset
Dim strAI As String ' string to contain list of competitors


Set db = CurrentDb


Set rs = db.OpenRecordset("SELECT tblaccountidstest.strAI FROM tblaccountidstest")
strAI = ""

Do Until rs.EOF = True
If strAI = "" Then
strAI = rs1!AI
Else
strAI = strAI & ", " & strAI
rs.MoveNext
Loop

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

End Sub

 

[willir]

Whoops...

  Do Until rs.EOF = True
    If strAI = "" then    
      strAI = rs1!AI
    else
      strAI = strAI & ", " & strAI
    [b][COLOR=blue]End If[/color][/b]
    rs.MoveNext
  Loop

 

[sprite]

Thanks , I hate to be a pest but now I'm getting a "Object Required" message. When I debug, the line: "strAI = rs1!AI" is hilighted in yellow.
Do you know why that would happen?
Thanks,
Sprite

 

[lespaul]

because I modified some code I found in another forum here. You need to change it to

strAI = rs!AI

not rs1

Leslie

 

[sprite]

Me again. I think we are getting closer. Why would I get an error message that says "Run Time Error 14, Out of String Space"? I thought maybe there were too many numbers (there were 30 total) so I deleted some to test just 6. I didn't get an error and I think it worked but where would I see the results? I thought it might change my table or create a new table. I'm missing something in the code to tell it to do that, right?
Thanks.

 

[lespaul]

You're right, there's nothing in the code that does anything with the string you have created.

What do you want to do with it? Display it on a form? in a report?

I don't know about the out of string space error or how to correct it. How long a string are you generating?

leslie

 

[sprite]

Maybe I'll be able to figure out the string space problem. I need to create a new table that has 2 columns "BL" and "AI" that looks like the example in my first post.
Thanks.

 

[lespaul]

why would you want to create a new table that stores this information (again! - duplication is non-normalized)?

You can use this function you just created, to put the data in the comma list anytime you need it.

Again, what do you want to do with the information?

leslie

 

[sprite]

I have to display it in a table that can be exported to Excel by the end user. It also has to be displayed in a report for the end user.
Thanks,
Sprite

 

[lespaul]

So, you can do all that without creating a new table.

Leslie

 

[sprite]

Leslie,
I have no idea how to do that though. I thought it might be easier to create the table. If you can help me, that would be greatly appreciated.
Sprite

 

[PHV]

You can almost do the same things with saved queries as with tables.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sprite]

PH or Leslie,
I have been trying to figure out how to take the string and put it into a format that is usable (query, tbl, etc.) for a week now. I'm brain dead. Please help. Again, here is the code I've got:

Private Sub CreateAIList_Click()

Dim db As Database
Dim rs As Recordset
Dim strAI As String ' string to contain list of competitors


Set db = CurrentDb


Set rs = db.OpenRecordset("tblaccountidstest")
strAI = ""

Do Until rs.EOF = True
If strAI = "" Then
strAI = rs!AI
Else
strAI = strAI & ", " & strAI
End If
rs.MoveNext
Loop

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

End Sub

 

[PHV]

You may consider write a public function callable from a query:

Public Function GetAIList(BL)
  Dim db As Database
  Dim rs As Recordset
  Dim strAI As String ' string to contain list of competitors
  Set db = CurrentDb
  Set rs = db.OpenRecordset("SELECT AI FROM tblaccountidstest WHERE BL=" & BL)
  strAI = ""
  Do Until rs.EOF
    If strAI = "" Then
      strAI = rs!AI
    Else
      strAI = strAI & ", " & strAI
    End If
    rs.MoveNext
  Loop
'release the memory
  Set rs = Nothing
  Set db = Nothing
  GetAIList = strAI
End Function

Write this function in a code module.
And then you can create a query like this:
SELECT BL, GetAIList(BL) FROM tblaccountidstest ORDER BY BL;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sprite]

Oh, wow, we're gettin real close now. I did as you suggested and here is what my output looked like (at bottom). I think there maybe just a little somethin that needs to be tweaked. What do you think?

My tblaccountidstest table looks like this:

BL AI
111805332 3444548
133060812 2822392
133060812 7138524
156672183 2870510
156672183 2879737
156672183 7138514

The query results returned this:

111805332 3444548
133060812 2822392, 2822392
156672183 2870510, 2870510, 2870510

Thanks, pls let me know what to do to fix.

 

[lespaul]

small change:

Public Function GetAIList(BL)
  Dim db As Database
  Dim rs As Recordset
  Dim strAI As String ' string to contain list of competitors
  Set db = CurrentDb
  Set rs = db.OpenRecordset("SELECT AI FROM tblaccountidstest WHERE BL=" & BL)
  strAI = ""
  Do Until rs.EOF
    If strAI = "" Then
      strAI = rs!AI
    Else
      strAI = strAI & ", " & [COLOR=red]rs!AI[/color]
    End If
    rs.MoveNext
  Loop
'release the memory
  Set rs = Nothing
  Set db = Nothing
  GetAIList = strAI
End Function

Leslie