Merging records in MS Access

Are there cases where a reference number exists more than twice?

Yes, unfortunately it's pretty random with some cases not split at all, and others split a number of times.

I am working a function that you can run which will combine all the data as you want.  Give me a few mins.

What type of data are in the reference and case notes fields?  numbers, text?

How many no. of times repeat takes place say  max 3, 4 or 5 etc

It sounds as if you might need a one-to-many relationshiup between Reference and Case Notes.  That would allow multiple Case Notes per reference, which might be better than merging them.  It would also allow a Date field to be in the Case Notes table, so you would have a date for each note.

pendulum,

see the sample db in this thread

https://www.experts-exchange.com/questions/27673299/combine-informaiton-on-two-rows-into-1-row.html?anchorAnswerId=37840943#a37840943

I concur with  Helen_Feddema, as it's easier to select CaseNotes from a normalized one-to-many relationship between Reference and CaseNotes.
Showing multiple CaseNotes per Reference or References per CaseNote (when a CaseNote can occur multiple times) is easier from two so-called "normalized tables".

Hi,

Thanks for the help on this so far. I would prefer to merge if possible as the fragmented records cause confusion with further work that needs to be done on this data.

@ masterjojobinks

I am trying to use your example database, but have come across a couple of issues. Firstly a run-time error 6: overflow. I think this was caused because there are a large amount of records in the d/base. I changed the inner and outer loop dims to Long and that part worked fine.

However, when I re-run the macro I get a run-time error 3075 - syntax error, along with the text of one of the case notes. Debug highlights this line of code:

CurrentDb.Execute (strAppendSQL)

Select allOpen in new window

I guess something in the content of the case note is confusing the macro? Is there some way of working around this?

Thanks again.

Yes, it can be worked around.  The append SQL is written specifically for dealing with a text field which only allow 255 characters.  Is the data being entered into the case notes field grow to more than 255 characters?  If so then you can change the code to make that field a memo field rather than text.  Replace this:
    strCreateSQL = "CREATE TABLE " & strNewTableName & " (" & _
                    "Reference TEXT(255), " & _
                    "CaseNotes TEXT(255) " & _
                    ");"
with
    strCreateSQL = "CREATE TABLE " & strNewTableName & " (" & _
                    "Reference TEXT(255), " & _
                    "CaseNotes MEMO " & _
                    ");"

Yes, the CaseNotes field is a memo rather than text. I have updated the code above, but unfortunately I still get the issue I mentioned.

It does appear that Access is trying to interpret the content of the CaseNote rather than just pasting it:

are there any Quote markes in the case notes?

As masterjojobinks mentions, quotes will "break" the SQL string.
To eliminate this risk you can use the Replace function to change a " into a "" string (this will embed the " in the final string.
Or you could replace the " with a single quote.

You are going to have to show me where the error is happening so I can get an idea of the data you are dealing with.  If you hit debug you can go to the immediate window and use the DEBUG.PRINT command to see which reference and case note is causing the error.

While the line of code is highlighted yellow, in the immediate window type:
DEBUG.PRINT rsRead("Reference")
This will give you the reference which you can then take the case notes and see what special characters might be in the field which could cause the issue.

Issue was my end. All sorted and works great. Thanks for all your help.