Trying to Clean up Access 2007 Database and Tables

Topic

New Reply

I am a newbie to Access, but am the geekiest of the group.

A friend has a practice with patients. Years ago, someone set up a database in Access for patient information. Multiple clerks enter the information for new patients, and we have ended up with 3 slightly different tables — all have the basics, like name, address and phone number, but other data such as cell phone and e-mail address is present for patients entered in one table, but not in another. AND, some patients are in more than one of the tables. Each patient has a unique patient ID number.

I have worked with the staff to determine what key data they want/need for each patient, and I have created a form in Access so that clerks can just use the form and not go into the database/tables portion of Access. The form for Access follows the sheet the patients fill out, so that will make things even easier. So we’re good moving forward.

My big question is what to do with the data that has already been entered. There are about 7000 different patients. How can I get them together in a single table, and how can I get rid of the duplicate entries? The challenge with the duplicates is (1) there are different data elements for the same patient, depending on which table (Patients1 or Patients2) and (2) even the same data element, such as Address, is Address in one table and StreetAddress in the other. The data, 124 Main Street, is the same — the label is what’s different.

I started by dumping all the info into Excel, but am having trouble figuring out how to automatically filter. I now have two or three rows for each patient in Excel, depending on the patient.

What I am hoping to do automatically is to combine all the data for each patient. If record one has data elements 1,2,3,5,8,9 and the other has 1,2,3,4,5,7,10,11, I would like to have one record with data elements 1,2,3,4,5,7,8,9,10,11 and not 1,1,2,2,3,3,4,5,5,7,8,9,10,11. I can work in Access with specific instructions or Excel — whichever would get me where I need to be. I hope this is a clear explanation, but let me know if you need more info.

I appreciate any help you can send my way!

Reply | Quote

Replies

I would start by making a table (probably the one you are using for new records entered using the form you mentioned), and then for each of the 3 tables, figure out where the data in those tables should go in your new table. Then you can create an append query that maps the data from the old table to the new table. So you would end up with three append queries to copy all of the data from the original 3 tables. You will likely want to do that process more than once, so I would create a test database that is separate from the the one where live data is being entered, and that will let you try things and if it doesn’t quite work right, go back and do it over.

Once you get all of the data into a single table with consistent field names, then you can set it up to view old records for each patient in a form and figure out what duplicates are there and need to be removed, and what can co-exist. But single table designs often have challenges, as data changes over time – e.g. phone numbers and postal addresses. I would be inclined to do what is called normalization so that you only need one record for patient, but multiple records in a related table that store information about patients, such as notes about a visit, symptoms, and I would put address information into a separate table. In the long run it will make the database more user friendly and more adaptable to change.

Feel free to post additional questions as you work your way through this – we’ve done a number of projects like this over the years, and it can actually be fun when it works well in the end.

Reply | Quote

Hi sheltieMom,
Here’s one way to handle the data elements that appear in more than one table. I’ll use patient_name as an example. Create a table (let’s call it “PNameTmp”) that has only patient_ID and patient_name. Then populate it with all distinct combinations of [patient_id, patient_name] from the three original tables, sorted by patient_id. The resulting rows might resemble the following:

1 | Ann Aardvark
1 | Annie Aardvark
2 | Bonnie Beetle
3 | Charles Centipede
3 | Charlie Centipede
3 | Chuck Centipede
etc.

Now, you would need to manually inspect this table for any patient_id that appears more than once, and delete all but one row for a given patient_id. This might result in the following:

1 | Ann Aardvark
2 | Bonnie Beetle
3 | Charles Centipede
etc.

These steps (starting with creating an additional “tmp” table) would be repeated for each field that appears in at least two tables.

The nice part is that when the above has been accomplished, you will need to visit only one table for the values of any given field. For example, the values for patient_name could come strictly from the PNameTmp table.

For any field that appears in only one of the original three tables, you would obtain its values from that one original table.

Hope this helps,
Dave

Reply | Quote