Data Merge

[Tony Ramirez]

Hello...I have a table that contains data like this. I am trying to get this
all into one field. Is there a way to "merge" this data. Keep in mind that I
dont know how many text lines this might have...could range from one to ten
or more. I can think of a couple convulted ways to do this...hoping I can
find something easier. Again I want the output to be just text block.
Sample Data :

PO_NUMBER TEXT
PO-010654 NEVER RECEIVED FOR JOB CS-010027. 4/20/01 @ 11:13
PO-010654 CANCELLED VERBALLY TO ROBB @ UNITED.
PO-010654 TOOK TOO LONG!
PO-010747 CANCEL PO 04/04/01
PO-010747 EXPEDITE DELIVERY.
PO-010799
PO-010827
PO-010869
PO-010907 CANCEL PER GREG STEFER DUE TO LONG LEAD TIME
PO-010907 DURABLE CONTROL PO-010958. DELETE PART NUMBERS
PO-010907 SPECIFICALLY FOR THIS PO.

 

[Tony Ramirez]

No not at this point...and I guess the only one I would be able to create
would just be an autonumber field. So I want PO to go to this table and write
a complete sentence instead of these fragments. Thanks so much! Tony

 

[KARL DEWEY]

Do you have a primary key field?

 

[Damon Heron]

Do you want each distinct PO to have all of the text comments grouped under
one? If so, this kludge will work:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim myvar As String
Dim chkstr As String
Dim trimit As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("testdata", dbOpenDynaset)
chkstr = rs!field1
Do While Not rs.EOF
If chkstr = rs!field1 Then
myvar = myvar & rs!field2 & ", "
Else
myvar = chkstr & ": " & myvar
trimit = Len(myvar)
myvar = Left(myvar, trimit - 2)
Debug.Print myvar ' each debug print has all of the comments for each
PO#
myvar = rs!field2 & ", "
chkstr = rs!field1
End If
rs.MoveNext
Loop
myvar = chkstr & ": " & myvar
trimit = Len(myvar)
myvar = Left(myvar, trimit - 2)

Debug.Print myvar ' This handles the last record's comments

rs.Close
Set db = Nothing
Set rs = Nothing


HTH
Damon

 

[Tony Ramirez]

Thank you...but I dont get it. Where would I put this statement?

Thanks for any help you could give me.

Tony

 

[Duane Hookom]

Can we assume you want to combine the TEXT from PO-010654 together and keep
it separate from other POs? You seem to be missing a field that would
identify the sequence of the TEXT.

There is a generic concatenate function at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4 that should
meet your needs.

 

[Damon Heron]

Lets take a step back. You have a table that you want to combine the text
for each distinct PO into a single field in what I assume is another table.
So your data in the new field in the new table would be:
First Record= PO-010654: blah blah, all the text comments for that PO #.
2nd Record= PO-010747:blah,blah
etc.
So, my code would go on a form with a command button that has the code as a
click event. You would need to substitute your table name, your field
names, and where the debug.print lines are, make an array with a counter to
increment each time you create a new record
so - debug.print myvar
becomes
myarray(x)= myvar ' where x is the counter in the loop.

Now you have an array filled with all the new records. You can then
manipulate the array anyway you desire, and write it to your new table by
any method you choose -a sql string, or opening a recordset for the new
table and using .AddNew

Since I have no idea why you want to do this, or what your skill level is,
that is about it for now. Play with test data and see if you can get the
results you want.

Damon