Concatenating text fields from different records

[stephen81]

We use two different systems in our office that have notebook facilities. One of them stores only a limited number of characters in the note field per record and as such, some notes actually span several records.
The other system stores the whole note in one record.
We want to transfer all the notes from the first system (1 note over several records) to the second system (1 whole note in 1 record).
I have a table with the notes from the first sytem which looks something like
Note1 - Line1 - Text
Note1 - Line2 - Text
Note2 - Line1 - Text
Note3 - Line1 - Text
Note3 - Line2 - Text
Note3 - Line3 - Text

What I want to do is, for each note, combine the separate lines into one record.
I have been able to do this in Excel with some formulas (see attachment) but am wondering if it is possible to do this in an Access Query (i.e. GroupBy the note number and have a calculated field combining the individual lines of the note, or something similar)?

 

[neileg]

If this is a one off transfer, I would use Excel, since it does what you want. Data cleansing and data type changes are often easier in Excel. The problem is that in Access, the position of the rows in a table is not important. In Excel it is.

If you want to do it dynamically because you are combining two sets of data on the fly, you can do it in code, but it's beyond my level of programming knowledge .

 

[stephen81]

It is a one off transfer and I'd happily do it in Excel as it appears easier. The problem is that there is a limit of ~65000 rows in Excel. Unfortunately I have getting on for 2,000,000 rows to deal with which would mean going through the same process about 30 times.
If someone can give me some tips on how I can go about this in Access that would be fantastic.

 

[WayneRyan]

Stephen,

Just a quick VBA shot at it:

Dim dbs As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strNote As String

Set dbs = CurrentDb
'
' Get ALL DISTINCT User_Refs from the Notebook
'
Set rst1 = dbs.OpenRecordset("Select Distinct(User_Ref) From YourNotebookTable")
While Not rst1.EOF and Not rst1.BOF
   '
   ' For each User_ref, append all of their notes
   '
   Set rst2 = dbs.OpenRecordset("Select * " & _
                                "From YourNotebookTable " & _
                                "Where User_Ref = '" & rst1!User_Ref & "' " & _
                                "Order by User_Ref, NoteID"
   strNote = ""
   While Not rst2.EOF And Not rst2.BOF
      strNote = strNote & rst2!Note
      rst2.MoveNext
      Wend
   '
   ' Insert the new record into the Production table
   '
   DoCmd.RunSQL "Insert Into NoteTable (User_Ref, NotePad_Date, Account_Ref, Complete_Note) " & _
                "Values ('" & rst1!User_Ref & "', #" & rst2!NotePad_Date & "#, '" & _
                rst2!Account_Ref & "', '" & strNote & "');"
   Set rst2 = Nothing
   rst1.MoveNext
   Wend

Obviously the field/table names need some work, but the concept works.

You could also make a Public Function, pass it a User_Ref, and it would do
the "rst2" stuff in the loop. Then you could just make an Append query, referencing
the function.

Wayne

 

[stephen81]

Thanks Wayne, that seems to be doing what I wanted.
I am now running into problems however with notes that have apostrophe's in them. I get a runtime error '3075' (missing operator). I can only guess that it is treating the apostrophe within the text field as part of the code.
I will search the forum as I would imagine I'm not the first person to have that problem, unless anyone has a quick answer for me?

 

[stephen81]

Problem solved, I hope. I just replaced

'" & strNote & "'

with

""" & strNote & """

in the SQL.

Is there anything I should be aware of in terms of problems created by doing this?

 

[WayneRyan]

Stephen,

No, that's a standard way to handle cases where you have single-quotes in
the data. I don't think there is a way to change SQL's delimiter to another
character like "`".

Wayne

 

[stephen81]

OK, problem not quite solved. As well as some notes containing apostrophes ('), it seems some notes also have speech marks (") as I discovered last night after running this code for a good couple of hours to convert my notes.
Any ideas how I can cope with speech marks as well?

 

[WayneRyan]

Stephen,

When the INSERT command has BOTH single/double quotes in it, I've had to go
to DAO.

Dim db As DAO.Database
Dim rst As DAO.RecordSet

Set db = CurrentDb
Set rst = dbs.OpenRecordSet("Select * From NoteTable")
'
' Then instead of the INSERT command
'
rst.AddNew
rst!User_Ref = rst1!User_Ref
rst!COmpleteNote = strNote
' Rest of fields here
rst.Update

Wayne