[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

Merging records in MS Access

Hi, I'm guessing there is probably a simple answer if you know how.

I have a table with two fields:
Reference
CaseNotes

In some instances, the records have been split so I have a number of records with the same reference number, but different CaseNote fragments.

I need to do a one off data clean so that any records with an identical reference number will be merged, leaving a single reference number and a merged CaseNote. For example:

Reference         CaseNotes
1234                 ABCD
1234                 EFGH
4321                 DCBA

becomes:

Reference         CaseNotes
1234                 ABCDEFGH
4321                 DCBA

Thanks for your help.
0
pendulum
Asked:
pendulum
  • 7
  • 4
  • 2
  • +3
1 Solution
 
Joe OvermanEngineerCommented:
Are there cases where a reference number exists more than twice?
0
 
pendulumAuthor Commented:
Yes, unfortunately it's pretty random with some cases not split at all, and others split a number of times.
0
 
Joe OvermanEngineerCommented:
I am working a function that you can run which will combine all the data as you want.  Give me a few mins.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Joe OvermanEngineerCommented:
What type of data are in the reference and case notes fields?  numbers, text?
0
 
karunamoorthyCommented:
How many no. of times repeat takes place say  max 3, 4 or 5 etc
0
 
Joe OvermanEngineerCommented:
Attached is a sample database which contains a function called cleanup.  What this function does is reads a table called sheet1 which contains the reference data and case notes data.  The function combines all the data from sheet one with the same reference number into a new table called Sheet1Combined.  See if this meets your needs.
Database1.mdb
0
 
Helen FeddemaCommented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
0
 
ggzfabCommented:
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".
0
 
pendulumAuthor Commented:
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)

Open 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.
0
 
Joe OvermanEngineerCommented:
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 " & _
                    ");"
0
 
pendulumAuthor Commented:
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:

Error Message
0
 
Joe OvermanEngineerCommented:
are there any Quote markes in the case notes?
0
 
ggzfabCommented:
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.
0
 
Joe OvermanEngineerCommented:
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.
0
 
pendulumAuthor Commented:
Issue was my end. All sorted and works great. Thanks for all your help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now