Merge similar records in Access 2007

[khoward]

Hi,

I have an Access 2007 database that contains customer contact information. There are over 8,000 that include name, organization (as a look-up column), email, phone, address, and events that each person should be invited to (also a look-up column).

Separate from this is an Excel spreadsheet that contains the some of the same information, with about 10,000 records. The overlap with duplicate names/records is probably about 50%, but of these, the Excel would have more information than the Access file, though Access does have a field that we don’t want to overwrite.

I know that can import the Excel spreadsheet into Access, but at that point is there a relatively easy way to find similar items (based on first name, last name, organization) and then merge those records - not just delete one of them? The Access record might have the name, organization and email address; while the Excel file might have the name, organization, phone number and mailing address - so I'd want to have 1 record for that contact which included all fields. Any help with this would be greatly appreciated!

[patjones]

Hi -

This process is much easier if there is something in each record that is unique to that particular person. Usually that would be something like an SSN or employee ID or customer ID, etc.

Since I'm assuming you don't have a field like that in your data, I would ask whether or not there is an email or phone number for each record in both sets of data (the one in Access and the one in Excel). If so perhaps you can find matches on that basis - provided that each phone number or email belongs to only one person in the database.

Barring the possibility of doing this merge on the basis of email or phone number, you would need to create a query that looks for records that have the same first and last name - and when such a match is found, form the new record by putting in whatever fields you want from the two old records.

[khoward]

Unfortunately I don't have any unique identifiers. Phone numbers are frequently the organization's main number, so mulitple people could have the same. Similarly, we'll sometimes have 1 person, usually an admin assistant, who has their email address listed for multiple people.

Going with your suggestion below, can you tell me how I would set up the query the way you describe?

Barring the possibility of doing this merge on the basis of email or phone number, you would need to create a query that looks for records that have the same first and last name - and when such a match is found, form the new record by putting in whatever fields you want from the two old records.

Thank you for your help!

[patjones]

One way to do this might be to create a composite key that consists of both fields, first and last names. Before that however, try the procedure below, which uses the Query Design view in Access.

What you would do is import the Excel data into Access. If you're using Access 2007, go to the ribbon up top and see External Data > Import Data > Excel and then follow the prompts to import the data into a new table in the database.

Once you have the Excel data in a new table, and have all the columns formatted in the same manner as the columns in the comparison table, go to Create > Query Design, and add the two tables to the query. Select Make Table up in the ribbon and type in the name of the table that will result from this query.

Notice that when you double click a field name in one of the tables, it inserts it in the grid below. Do this for both the first and last name fields in one of the tables. In the criteria box for each field, type in the corresponding field name in the other table, in the form [table name]![field name]. This will ensure that the query picks out records that have the same first and last name in both tables.

To add in other fields to display in the results, just double click them and they will be added to the grid. Up in the ribbon, click Run and the query should run. Note that sometimes you might need to click Query Tools in order to display the query options in the ribbon.

This process will find all records that match up. There will also be records in the Excel table which don't match up (i.e. aren't in the Access database already), which you could find easily with the Unmatched Query Wizard under Query Wizard in the ribbon.