Solved

Merging records in MS Access

Posted on 2012-04-13
16
424 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:Joe Overman
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:Joe Overman
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 8

Expert Comment

by:Joe Overman
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:
Joe Overman 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37844304
0
 
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:Joe Overman
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:Joe Overman
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:Joe Overman
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

777 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