Building a String from Multiple records 2

[Hattrick16]

I need to build a string from multiple records in a table that share common elements. Here is a sample of the data

Data:

Name Adress SS # Date Sales Code
Nick 123 State 123-45-6789 1/2/2005 100.00 A
Nick 123 State 123-45-6789 1/3/2005 200.00 B
Nick 123 State 123-45-6789 1/4/2005 300.00 C
Nick 123 State 123-45-6789 1/5/2005 400.00 D

I need an ouput that will Look like this :

Name Address SS # Date Sales Code Date2 Sales2 Code2 ....
up to 5 transactions per Name Address and SS # Each field has a fixed length. This will ultimately exported into a text file to be imported into a 3rd party application.

 

[jrbarnett]

the easiest way is to open the data as a recordset and then loop through the recordset. These can then be put into a string variable or written to a text file as per your requirements.

John

 

[Hattrick16]

I thought that would be the general approach but it is beyond my skills. Can you Provide a little more help.. or example

 

[Hattrick16]

Can Some one provide me a little more help? I am on a tight time line....

 

[Hattrick16]

Can Some one provide me a little more help?

 

[Tranman]

Here ya go, Hattrick:

Private Sub BuildString()
Dim intPtr As Integer
Dim rs As New ADODB.Recordset
Dim strData As String

Open <textfilename> for output as #1
rs.Open "Select * from cust where name = 'Nick'", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
  For intPtr = 0 To 5 'column numbers are zero significant-this is 6 total columns
    strData = strData & rs.Fields(intPtr) & " "
  Next
  Debug.Print strData  'just so you can single instruct and see what you're building
  rs.MoveNext
Loop

print #1, strData
Close #1

rs.Close
Set rs = Nothing
End Sub

Write back if you need more help.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[Hattrick16]

Tranman.

Thanks for the Help But Just a Few questions..

1. In the line;
rs.Open "Select * from cust where name = 'Nick'",

It looks for Nick in the Cust field. However this field is populated with many different customers. If this is hard coded how will it look for the next customer in the DB.

Here is a more explicit Data example:

Name Adress SS # Date Sales Code
Nick 123 State 123-45-6789 1/2/2005 100 A
Nick 123 State 123-45-6789 1/3/2005 200 B
Steve 999 Water 987-65-321 1/4/2005 300 C
Steve 999 Water 987-65-321 1/5/2005 400 D

There Can be up to 35 Records for a Name/Address/SS Combo.

Final Output should be two records.

Nick 123 State 123-45-6789 1/2/2005 100 A 1/3/2005 200 B
Steve 999 Water 987-65-321 1/4/2005 300 C 1/5/2005 200 D

 

[Tranman]

Hattrick,
Ok, I gave you the Reader's Digest condensed version. When responding to posts like yours, it's difficult to know how much help the person really needs.

Why don't you go ahead and try to get the hard coded version to work, so you'll see how that goes, and I'll bang out some code that will build a record for each Name/Address/SS.

I'm having a sort of slow morning--just one 30-minute meeting scheduled, so it won't take long for me to get back to you on this.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[Tranman]

Hattrick,
Give this a try. It works fine with my very limited dataset.

Just a suggestion--in the future, if you're designing a table, don't name fields anything that contains a space character (like SS #). When you do that, it causes you to have to surround the field name with square brackets [] every time you refer to the field in code. A field name like SS_NBR is much better.

Anyway, here's the code:

Private Sub BuildString()
Dim intPtr As Integer
Dim rs As New ADODB.Recordset
Dim rsDist As New ADODB.Recordset
Dim strData As String

Open "C:\MyTextFile.txt" For Output As #1

rsDist.Open "Select distinct Name, Address, [SS #] from customer order by name", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do While Not rsDist.EOF
  rs.Open "Select * from customer where name = '" & rsDist.Fields("Name") & _
    "' and address = '" & rsDist.Fields("Address") & "' and [SS #] = '" & _
    rsDist.Fields("SS #") & "' ", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  strData = ""
  Do While Not rs.EOF
    For intPtr = 0 To 5 'column numbers are zero significant-this is 6 total columns
      strData = strData & rs.Fields(intPtr) & " "
    Next
    Debug.Print strData  'just so you can single instruct and see what you're building
    rs.MoveNext
  Loop
  Print #1, strData
  rs.Close
  Set rs = Nothing
  rsDist.MoveNext
Loop

Close #1

rsDist.Close
Set rsDist = Nothing
End Sub

Please let me know if it works for you.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[Tranman]

Took a closer look at your desired output. Here's code that does not repeat the n/a/s for each row of the table.

Private Sub BuildString()
Dim intPtr As Integer
Dim rs As New ADODB.Recordset
Dim rsDist As New ADODB.Recordset
Dim strData As String

Open "C:\MyTextFile.txt" For Output As #1

rsDist.Open "Select distinct Name, Address, [SS #] from customer order by name", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do While Not rsDist.EOF
  rs.Open "Select * from customer where name = '" & rsDist.Fields("Name") & _
    "' and address = '" & rsDist.Fields("Address") & "' and [SS #] = '" & _
    rsDist.Fields("SS #") & "' ", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  strData = ""
  
  For intPtr = 0 To 2
    strData = strData & rsDist.Fields(intPtr) & " "
  Next
  
  Do While Not rs.EOF
    For intPtr = 3 To 5 'column numbers are zero significant-this is 6 total columns
      strData = strData & rs.Fields(intPtr) & " "
    Next
    Debug.Print strData  'just so you can single instruct and see what you're building
    rs.MoveNext
  Loop
  Print #1, strData
  rs.Close
  Set rs = Nothing
  rsDist.MoveNext
Loop

Close #1

rsDist.Close
Set rsDist = Nothing
End Sub

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[Hattrick16]

TranMan,

I am adapting it to my exact data. The actual dataset has 34 fields and is over 700k records. I whould have it completed tomarrow.

Thanks for you help. I think I am having a Problem with names that have a ' in them. Not sure still testing.

Nick

 

[Hattrick16]

It Workedlike a charm however The third Party softrare will only accept a Coma delimited file How can I Modify so it is comma delimited

 

[Tranman]

Hi Hattrick,
Sounds like you're getting close.

For a CSV (comma-separated values) file, replace BOTH lines that say:
strData = strData & rsDist.Fields(intPtr) & " "
with this:
strData = strData & rsDist.Fields(intPtr) & ","

If it coughs on the final trailing comma in each line(it may or may not, depending on the nature of your third-party software), add this line just before the Print #1 command:

strData = Left(strData, Len(strData) - 1).

That will trim off the final comma from each record.

Let me know if it works.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[Hattrick16]

Worked like a charm.

Now trying to write the code to take the ' out of the name fields ie. O'Brien. Thinking of a simple SQL find and replace.

Thanks for your help

 

[Tranman]

Thanks for the *. You were starting to sound a bit desperate yesterday morning.

A simple string replace might do the job for you:

Private Sub ReplaceApp()
Dim strText As String
strText = "Bill O'Brien"
strText = Replace(strText, "'", "") 'Bill OBrien
End Sub

(Replaces all apostrophies with "", could do space " " if you like that better)

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[Hattrick16]

Thanks for the tip ...

There are a whole host of names with an ' in them. I Have to replace the ' in all of them so I was thinking of something like:

UPDATE TestData SET
LastName = Replace ( LastName, "'", "" ),
FirstName = Replace ( FirstName, "'", "" ),

But I can not get it to work.
Can I just put this in a module? I Think I need to open my record set first.

 

[Tranman]

Yeah, you're being hosed when you try to use those fields that have 's as criteria, so you need to get rid of them before you do any of your recordset stuff.

Your query should work fine to replace the 's. Could it be just the extra , at the end, or is that a typo?

Yes, you can run the sql update query in the module:

CurrentProject.Connection.Execute "Update TestData set LastName = Replace(LastName,""'"","""")"

Looks weird, but that's the deal with imbedded quotes...

Let me know if that works.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[foto66]

here's something simple:

SELECT [Name] & ' ' & [Adress] & ' ' & [SS #] & ' ' & [Date] & ' ' & [Sales] & ' ' &

 As myString

start a new "query", choose "SQL view" from the menu and paste
that in (i assume those are your field names--if not, you must edit what i've typed out)

 

[Tranman]

foto66,
Yeah, you're right about that. He could also accomplish the same thing with the GetString method of the ADODB Recordset Object.

Still, using fields that contain apostrophes as criteria is a pain, and Hattrick would probably be better off eliminating them from his table.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[Hattrick16]

TranMan,

I still cannot run my update to remove the ' from the names.

I copied the code into my module right after I open the output file and before I do all my record set stuff. I get and UNDEFINED FUNCTION ERROR in my SQl line.