Solved

Merging records in MS Access

Posted on 2012-04-13
16
395 Views
Last Modified: 2012-08-14
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
Comment
Question by:pendulum
  • 7
  • 4
  • 2
  • +3
16 Comments
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 37843230
Are there cases where a reference number exists more than twice?
0
 

Author Comment

by:pendulum
ID: 37843236
Yes, unfortunately it's pretty random with some cases not split at all, and others split a number of times.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 37843268
I am working a function that you can run which will combine all the data as you want.  Give me a few mins.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 37843378
What type of data are in the reference and case notes fields?  numbers, text?
0
 
LVL 7

Expert Comment

by:karunamoorthy
ID: 37843401
How many no. of times repeat takes place say  max 3, 4 or 5 etc
0
 
LVL 8

Accepted Solution

by:
masterjojobinks earned 500 total points
ID: 37843431
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 37843838
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37844304
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 5

Expert Comment

by:ggzfab
ID: 37847126
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
 

Author Comment

by:pendulum
ID: 37851033
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
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 37851124
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
 

Author Comment

by:pendulum
ID: 37851298
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
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 37851650
are there any Quote markes in the case notes?
0
 
LVL 5

Expert Comment

by:ggzfab
ID: 37852662
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
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 37853244
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
 

Author Comment

by:pendulum
ID: 37864866
Issue was my end. All sorted and works great. Thanks for all your help.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now