Merging duplicate data

[spenco]

Hi, I work with medical records and was given the task of merging 3 separate databases into a single Access file. I've been able to manipulate the databases so they are all formatted identically (albeit there are now a few "null"/non-applicable columns in many of the rows, but that's ok) and I've successfully imported and appended all of the files creating one long list of data. However, here's my quandary: each of these databases were originally created to track information regarding separate medical information, ie one looks at patients with eye disease, the other records info about patients with joint disorders, and the third details a specific group of patients' genetic information. I would guess that about half of these patients have some overlap and are in two or even all three of the databases, and unfortunately each database was created by separate medical researchers at different points in time, so the patient numbers do not match up between the databases (ie Jane Doe may be in 2 databases but her ID number is 223 in one and 394 in another). My task has been to merge all of this information into one spreadsheet that only lists each patient one time yet contains all of the information from all three of the databases (ie one row for Jane Doe that contains all of her eye disease info, her joint disease info, and her genetic info). I've figured out how to run duplicate queries based on the name and birthdate, and this shows me which patients are in more than one of the databases, but since each database has unique information I cannot just delete the other duplicate rows because in all actuality they aren't "duplicates" at all. The patient name and birthday is duplicate info, but the data is all unique. I have not been able to think of any possible way to successfully merge this information so that the remaining database only lists each patient one time yet still contains all of their medical info. I certainly don't mind having blank rows (ie in order to append all this info I had to combine the columns so that now a patient who did not have any eye disease will just have blanks in all of the eye disease columns but will have valuable data in the joint disease columns that follow). Any ideas? Thanks so much!

[Dal Jeanis]

Okay, so first, you need to forget that you've "successfully" merged the three tables. By doing so, you created a structure that almost certainly isn't normalized. You'll cause yourself lots of headaches if you just go forward.

You will save yourself a lot of work in the long run if you back up NOW and do the analysis first, and determine how the database needs to be structured to properly represent the information.

Then you'll determine the steps to merge the data into the correct form, and the steps to export it again.

First, head over to Access MVP Roger Carlson's site at http://rogersaccesslibrary.com and review his short tutorials on relational database design. Do a couple of the sample exercises, until you feel comfortable with how an entity relationship diagram works. It's not tough, it's just a structured way of looking at real life "entities". It will save you dozens of hours later.

Next, figure out what entities are represented by your three databases, and make an entity relationship diagram.

Finally, post your preliminary design here under the database design forum and we'll critique it. Once you have a solid design, we'll show you how to get it properly populated, and how to pull your desired reports out of it.

(HINT - The key word for how to pull combined reports from the three tables is "Left Join". It's not tough. But do the design first.)

[June7]

What you really have is 3 'flat file' tables? Is each table in separate Access db file?

If name and birthdate are truly a unique compound identifier and are definitely correct in all 3 tables, then use those as temporary compound primary key/foreign key.

Import the 3 tables into one db file.

Use a UNION query to create a 'master' dataset of all name/birthdate values from all 3 tables. Create a new table (Patients) with that query and add an autonumber field to the new table.

Now add a number field to each of the 3 tables.

Run 3 UPDATE queries. Each will join one of the 3 tables to the Patients table by linking on the name/birthdate fields. Update the new foreign key field with the autonumber value from Patients table.

Delete the name and birthdate fields from the 3 tables. Can also delete the original ID fields or rename as OldID for historical documentation.